JDBC operation database 06

There is a problem in synthesizing the jdbc operations in the previous several articles, that is, too many repetitive steps need to be written, which wastes time, so you can write a jdbc tool class, encapsulate some repetitive code, and call directly when necessary

Steps:
1. Write the JdbcUtil class as a common code package
2. Realize code and data separation, convenient for later modification and other operations. It's more troublesome to use parameter passing, so read the configuration file and define the configuration file JDBC info.properties

JdbcUtils utility class

import java.io.FileReader;
import java.io.IOException;
import java.net.URL;
import java.sql.*;
import java.util.Properties;

/**
 * @ClassName: JdbcUtils
 * @Description: TODO
 * @author: Doge_fang
 * @date: 2020/2/14  14:13
 *
 *jdbc Tool class, used to encapsulate the repeated code of jdbc operation database
 */
public class JdbcUtils {

    private static String url;
    private static String user;
    private static String password;
    private static String driver;

    //Database connection information can be placed in the configuration file
    //The configuration file only needs to read once to get the data, which can be written in the static code block
    static{
        try {
            //Create the properties collection class
            Properties pro = new Properties();
            //How to get files under src path -- classloader loader
            ClassLoader loader = JdbcUtils.class.getClassLoader();
            URL res = loader.getResource("jdbcinfo.properties");
            String path = res.getPath();
            pro.load(new FileReader(path));

            //Get data and assign values
            url = pro.getProperty("url");
            user = pro.getProperty("user");
            password = pro.getProperty("password");
            driver = pro.getProperty("driver");
            //Registration driven
            Class.forName(driver);
        } catch (IOException | ClassNotFoundException e) {
            e.printStackTrace();
        }

    }

    /**
     * Get connection method
     * @return Connection object
     * @throws SQLException
     */
    public static Connection getConnection() throws SQLException {
        return DriverManager.getConnection(url, user, password);
    }

    /**
     *Get execution sql object
     * @return
     * @throws SQLException
     */
    public static Statement getStatement() throws SQLException {
        return  JdbcUtils.getConnection().createStatement();
    }


    /**
     * Release resources
     * @param conn
     * @param state
     */
    public static void close(Connection conn,Statement state)  {
        if(conn != null){
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if(state != null){
            try {
                state.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    /**
     * Release resources
     * @param conn
     * @param state
     * @param ret
     */
    public static void close(Connection conn, Statement state, ResultSet ret){
        if(conn != null){
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if(state != null){
            try {
                state.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if(ret != null){
            try {
                ret.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

}

JDBC info.properties configuration file

jdbc connection operation

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * @ClassName: JdbcBase
 * @Description: TODO
 * @author: Doge_fang
 * @date: 2020/2/12  14:46
 *
 * Define a method to query the data of the T sys user table, encapsulate it as a collection, and return
 */
public class Jdbc_Base05 {
    public static void main(String[] args) {
        List<SysUser> list = new Jdbc_Base05().quaryAll();
        System.out.println(list);
    }


    public List<SysUser> quaryAll() {
        Connection conn = null;
        Statement statement = null;
        ResultSet rs = null;
        List<SysUser> list = null;

        try {
            //Get connection resources
            conn = JdbcUtils.getConnection();
            //Define sql
            String sql = "select * from userinfo;";
            //Get execution sql object
            statement= JdbcUtils.getStatement();
            //Implementation of sql
            rs = statement.executeQuery(sql);
            //Traverse result set, encapsulate object, load set
            //get data
            list = new ArrayList();
            while (rs.next()) {
                int id = rs.getInt("id");
                String username = rs.getString("username");
                int phonenumber = rs.getInt("phonenumber");
                Date createdate = rs.getDate("createtime");
                //Create and assign a t sys user object
                SysUser SysUser = new SysUser();
                SysUser.setId(id);
                SysUser.setUsername(username);
                SysUser.setPhonenumber(phonenumber);
                SysUser.setCreatetime(createdate);

                //Loading set
                list.add(SysUser);
            }

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JdbcUtils.close(conn, statement, rs);
        }
        return list;
    }
}

Published 30 original articles, praised 0, visited 786
Private letter follow

Tags: SQL Java JDBC Database

Posted on Fri, 14 Feb 2020 07:11:48 -0800 by p2003morris