Java learning notes 48 (DBUtils tool class I)

As can be seen from the example in the previous article, when adding, deleting, modifying and querying, a lot of code is repetitive,

Then, can we encapsulate the addition, deletion, modification and query into a class for the convenience of users

 

package demo;

/*
 *  Implement JDBC tool class
 *  Define a method to directly return the connection object of the database
 *  
 */
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class JDBCUtils {
    private JDBCUtils() {
    }

    private static Connection con;

    static {
        try {
            Class.forName("com.mysql.jdbc.Driver");
            String url = "jdbc:mysql://localhost:3306/mybase";
            String username = "root";
            String password = "xuyiqing";
            con = DriverManager.getConnection(url, username, password);
        } catch (Exception ex) {
            throw new RuntimeException(ex + "Database connection failed");
        }
    }

    /*
     * Define static method, return connection object of database
     */
    public static Connection getConnection() {
        return con;
    }

    // Close method
    public static void close(Connection con, Statement stat) {

        if (stat != null) {
            try {
                stat.close();
            } catch (SQLException ex) {
            }
        }

        if (con != null) {
            try {
                con.close();
            } catch (SQLException ex) {
            }
        }

    }

    // heavy load
    public static void close(Connection con, Statement stat, ResultSet rs) {
        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException ex) {
            }
        }

        if (stat != null) {
            try {
                stat.close();
            } catch (SQLException ex) {
            }
        }

        if (con != null) {
            try {
                con.close();
            } catch (SQLException ex) {
            }
        }

    }
}

 

Test class:

package demo;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

public class TestJDBCUtils {
    public static void main(String[] args) throws Exception {
        Connection con = JDBCUtils.getConnection();
        PreparedStatement pst = con.prepareStatement("SELECT sname FROM sort");
        ResultSet rs = pst.executeQuery();
        while (rs.next()) {
            System.out.println(rs.getString("sname"));
        }
        JDBCUtils.close(con, pst, rs);
    }
}

 

 

You can save data from a table to an object:

package demo;

public class Sort {
    private int sid;
    private String sname;
    private double sprice;
    private String sdesc;

    public Sort(int sid, String sname, double sprice, String sdesc) {
        this.sid = sid;
        this.sname = sname;
        this.sprice = sprice;
        this.sdesc = sdesc;
    }

    public Sort() {
    }

    public int getSid() {
        return sid;
    }

    public void setSid(int sid) {
        this.sid = sid;
    }

    public String getSname() {
        return sname;
    }

    public void setSname(String sname) {
        this.sname = sname;
    }

    public double getSprice() {
        return sprice;
    }

    public void setSprice(double sprice) {
        this.sprice = sprice;
    }

    public String getSdesc() {
        return sdesc;
    }

    public void setSdesc(String sdesc) {
        this.sdesc = sdesc;
    }

    @Override
    public String toString() {
        return "Sort [sid=" + sid + ", sname=" + sname + ", sprice=" + sprice + ", sdesc=" + sdesc + "]";
    }

}

 

package demo;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

/*
 *  JDBC Read the data table Sort, and encapsulate each row of data into the object of the Sort class
 *  Many Sort class objects are stored in the List collection
 */
public class JDBCDemo {
    public static void main(String[] args) throws Exception{
        //Use JDBC Tools,Get database connection object directly
        Connection con = JDBCUtils.getConnection();
        //Connect to get database SQL Statement performer object
        PreparedStatement pst = con.prepareStatement("SELECT * FROM sort");
        //Call query method,Get result set
        ResultSet rs = pst.executeQuery();
        //Create collection object
        List<Sort> list = new ArrayList<Sort>();
        while(rs.next()){
            //Get data for each column,Package to Sort In object
            Sort s = new Sort(rs.getInt("sid"),rs.getString("sname"),rs.getDouble("sprice"),rs.getString("sdesc"));
            //Encapsulated Sort object,Store in collection
            list.add(s);
        }
        JDBCUtils.close(con, pst, rs);
        //ergodic List aggregate
        for(Sort s : list){
            System.out.println(s);
        }
    }
}

 

 

There is a problem here. The data in our customized tool class can't be modified, so it's inconvenient to change the driver, user name, or connection address

Therefore, the parameters can be placed in the configuration file. When modifying, you only need to change the configuration file. You do not need to modify the source code, which is conducive to later maintenance

Configuration files are usually placed under scr files

Note here that when creating a file in the src directory, the file will also be copied automatically in the bin directory

Create the file database.properties and write the key value pair

diverClass=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/mybase
username=root
password=xuyiqing

To load a profile using an IO stream:

package demo;

import java.io.FileInputStream;
import java.io.InputStream;
import java.util.Properties;

public class PropertiesDemo {
    public static void main(String[] args) throws Exception {
        //Previous methods
        //FileInputStream fis1 = new FileInputStream("database.properties");
        //Here's a new method: class loader, from bin Load under directory
        InputStream in = PropertiesDemo.class.getClassLoader().getResourceAsStream("database.properties");
        Properties pro = new Properties();
        pro.load(in);
        System.out.println(pro);
    }
}

Output: {password = xuyiqing, url = JDBC: MySQL: / / localhost: 3306 / MyBase, diffclass = com. Mysql. JDBC. Driver, username = root}

Successfully loaded

 

So I wonder if I can connect to the database through the configuration file?

package demo;

import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

public class JDBCUtils {
    private JDBCUtils() {
    }

    private static Connection con;

    static {
        try {
            InputStream in = JDBCUtils.class.getClassLoader().getResourceAsStream("database.properties");
            Properties pro = new Properties();
            pro.load(in);
            String diverClass = pro.getProperty("driverClass");
            String url = pro.getProperty("url");
            String username = pro.getProperty("username");
            String password = pro.getProperty("password");
            Class.forName(diverClass);
            con = DriverManager.getConnection(url, username, password);
        } catch (Exception ex) {
            throw new RuntimeException(ex + "Database connection failed");
        }
    }

    public static Connection getConnection() {
        return con;
    }

    public static void close(Connection con, Statement stat) {

        if (stat != null) {
            try {
                stat.close();
            } catch (SQLException ex) {
            }
        }

        if (con != null) {
            try {
                con.close();
            } catch (SQLException ex) {
            }
        }

    }

    public static void close(Connection con, Statement stat, ResultSet rs) {
        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException ex) {
            }
        }

        if (stat != null) {
            try {
                stat.close();
            } catch (SQLException ex) {
            }
        }

        if (con != null) {
            try {
                con.close();
            } catch (SQLException ex) {
            }
        }

    }
}

Tags: Java SQL Database JDBC

Posted on Tue, 05 May 2020 00:19:00 -0400 by pod2oo5