Basic operation of JDBC

In order to reuse the code and improve the development efficiency, it is very necessary to design the addition, deletion, modification and query of JDBC database.

JDBC data operation steps are generally divided into the following five steps:

1, Load database driver class

• Class.forName("com.mysql.cj.jdbc.Driver");

2, Establish connection

• Connection connection = DriverManager.getConnection(url,user,password);

3, Create a PrepareStatement object for sending sql to the database and send the sql

• PrepareStatement statement = connection.PrepareStatement(sql); `

4, Fetch the return data from the ResultSet of the result set.

• ResultSet resultSet = statement.excuteQuery();

5, Close related resources and free memory

• *.close()

Using the Properties object

The Properties object can hold a set of key values. It passes driver Properties to the driver by calling the getConnection() method.

Create a new jdbc.properties file in the root directory (Anywhere)

mysql8 needs to set the time zone. There is a cj directory, but mysql5 doesn't. The time zone can be set to GMT8, indicating East Zone 8

url=jdbc:mysql://58.42.239.163:3306/jdbc?serverTimezone=Asia/Shanghai&characterEncoding=UTF8&useSSL=false
user=star
password=Star@123456

explain:

• Properties prot = new Properties();

  Create a profile object or read it using an IO stream

• InputStream input = JDBC1.class.getClassLoader().getResourceAsStream("jdbc.properties");

  Loading configuration files through loader

• prot.load(input);

  Read configuration file information through input stream

• prot.getProperty("url")

  Read configuration data through getXXX()

Add, delete, modify and query tool class

Load database driver class

Pass class Forname reflection loading mysql8 driver class

Because the driver class will be loaded every time you operate on the database. By using the static code block, you can directly load the class when it is loaded. If you execute it only once, you don't have to load it every time

public class JDBCUtility {
    //Load mysql driver class
    static {
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }

Establish connection

Method of encapsulating a connection into a tool

/**
 * Get connection
 *
 * @return Returns the created connection
 */
public static Connection getConnection() {
    Properties properties = new Properties();
    Connection connection = null;
    try (
            FileInputStream fis = new FileInputStream("jdbc.properties");
    ) {
        properties.load(fis);
        connection = DriverManager.getConnection(properties.getProperty("url"), properties);
    } catch (Exception e) {
        e.printStackTrace();
    }
    return connection;
}

Close connection and statement

Overloading three Close() methods, after using resources, adjust the method to close resources.

/**
 * Close connection
 *
 * @param connection connect
 */
public static void Close(Connection connection) {
    if (connection != null) {
        try {
            connection.close();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }
}

/**
 * Close statement connection
 *
 * @param statement connect
 */
public static void Close(Statement statement) {
    if (statement != null) {
        try {
            statement.close();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }
}

/**
 * Close the connection and statement connections
 *
 * @param connection connect
 */
public static void Close(Connection connection, Statement statement) {
    Close(statement);
    Close(connection);
}

General code for addition, deletion and modification

Pass the sql statement to be executed. You can select the parameters that meet the conditions

PrepareStatement is precompiled, compiled once and executed many times to improve efficiency and use sql conditions? "Can prevent sql injection problems

/**
 * General operations of addition, deletion and modification
 *
 * @param sql  sql statement to be executed
 * @param params Condition parameters
 * @return Returns the number of rows affected
 */
public static int executeUpdate(String sql, Object... params) {
    Connection connection = getConnection();
    PreparedStatement statement = null;
    int rows = -1;
    if (connection == null) {
        return rows;
    }
    try {
        connection.setAutoCommit(false);
        statement = connection.prepareStatement(sql);
        for (int i = 0; i < params.length; i++) {
            statement.setObject(i + 1, params[i]);
        }
        rows = statement.executeUpdate();
        connection.commit();
    } catch (SQLException e) {
        e.printStackTrace();
        try {
            connection.rollback();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    } finally {
        Close(connection, statement);
    }
    return rows;
}

General code for query operation

Get ResultSet by inserting statement

/**
 * Get ResultSet by inserting statement
 *
 * @param sql    sql statement to be executed
 * @param params Related condition parameters
 * @return Return ResultSet
 */
public static ResultSet executeQuery(String sql, Object... params) {
    Connection connection = getConnection();
    PreparedStatement statement = null;
    ResultSet resultSet = null;
    if (connection == null) {
        return null;
    }
    try {
        connection.setAutoCommit(false);
        statement = connection.prepareStatement(sql);
        for (int i = 0; i < params.length; i++) {
            statement.setObject(i + 1, params[i]);
        }
        resultSet = statement.executeQuery();
        connection.commit();
        return resultSet;
    } catch (SQLException throwables) {
        throwables.printStackTrace();
        try {
            connection.rollback();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            Close(connection, statement);
        }
    }
    return resultSet;
}

Common code for batch operation

@param sql is the sql statement to be executed

@param list requires the collection passed in

/**
 * Batch operation
 *
 * @param sql  sql statement to be executed
 * @param list Collection that needs to be passed in
 * @return Number of rows affected
 */
public static int executeBatch(String sql, List<Object[]> list) {
    Connection connection = getConnection();
    PreparedStatement statement = null;
    int rows = -1;
    if (connection == null) {
        return rows;
    }
    try {
        connection.setAutoCommit(false);
        statement = connection.prepareStatement(sql);
        for (Object[] objects : list) {
            for (int i = 0; i < objects.length; i++) {
                statement.setObject(i + 1, objects[i]);
            }
            statement.addBatch();
        }
        int[] ints = statement.executeBatch();
        rows = Arrays.stream(ints).sum();
        connection.commit();
    } catch (SQLException throwables) {
        throwables.printStackTrace();
        try {
            connection.rollback();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    } finally {
        Close(connection, statement);
    }
    return rows;
}

Overloaded excuteBathc method

@Two dimensional array passed by param objects

/**
 * Overloaded excuteBathc method
 *
 * @param sql     Executed sql statement
 * @param objects Passed 2D array
 * @return Returns the number of rows affected
 */
public static int executeBatch(String sql, Object[][] objects) {
    return executeBatch(sql, Arrays.asList(objects));
}

Main method test

code

public class JDBCTest {
    public static void main(String[] args) {
        //Add a piece of data
        String insertSql="insert into `user1`(`username`,`password`,`birthday`,`gender`,`is_delete`) value(?,?,?,?,?)";
        Object[] objects={"Xiaoguang","232434",new Date(),"male",true};
        System.out.println("Add data:");
        int rows = JDBCUtility.executeUpdate(insertSql, objects);
        System.out.println("Number of rows affected:"+rows);
        //Delete a row of data called Zhang Fei
        System.out.println("Delete data:");
        String deleteSql="delete from `user1` where `username`=?";
        int rows1=JDBCUtility.executeUpdate(deleteSql,"Xiao Fang");
        System.out.println("Number of rows affected:"+rows1);
        //Modify data with id 7
        System.out.println("Modify data:");
        String updateSql="update `user1` set `username`=\"Xiao Ni\" where id=?";
        int rows2 = JDBCUtility.executeUpdate(updateSql, 7);
        System.out.println("Number of rows affected:"+rows2);
        //Query all data of User entity
        System.out.println("query user Data in table:");
        String selectSql="select * from `user1`";
        List<UserEntity> list = JDBCUtility.getEntity(selectSql);
        for (UserEntity userEntity : list) {
            System.out.println(userEntity);
        }
        //Batch operation, adding two pieces of data
        System.out.println("Add two pieces of data:");
        String insertSql1="insert into `user1`(`username`,`password`,`birthday`,`gender`,`is_delete`) value(?,?,?,?,?)";
        Object[][] objects1={{"Xiao Xu","232434",new Date(),"male",true},
                             {"Xiaosheng","232434",new Date(),"male",true}};
        int rows3 = JDBCUtility.executeBatch(insertSql1, objects1);
        System.out.println("Number of rows affected:"+rows3);
    }
}

Result display

Data before operation

Data after operation

Get User entity

Designing the UserEntityMapper class

public class UserEntityMapper {
    public static List<UserEntity> getUser(ResultSet resultSet){
        List<UserEntity> list=new LinkedList<>();
        try {
            while (resultSet.next()) {
                int id=resultSet.getInt("id");
                String username = resultSet.getString("username");
                String password = resultSet.getString("password");
                Date birthday=resultSet.getDate("birthday");
                String gender=resultSet.getString("gender");
                Boolean is_delete=resultSet.getBoolean("is_delete");
                UserEntity userEntity = new UserEntity(id, username, password, birthday, gender, is_delete);
                list.add(userEntity);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }
}

Tool and method to get User entity

    /**
     * Get User entity
     *
     * @param sql    Executed sql statement
     * @param params Required parameters
     * @return Returns a collection of entities
     */
    public static List<UserEntity> getEntity(String sql, Object... params) {
        ResultSet resultSet = executeQuery(sql, params);
        return UserEntityMapper.getUser(resultSet);
    }
}

service design

The service layer, also known as the business logic layer, is mainly used to implement complex business logic, but does not directly execute sql query. sql query is the responsibility of dao.

UserService interface

public interface UserService {
    /**
     * Query data by ID
     * @param id    /
     * @return  /
     */
    UserEntity getUserById(Integer id);
}

UserServiceImpl implementation class

public class UserServiceImpl implements UserService {
    private final UserDao userDao = new UserDaoImpl();

    @Override
    public UserEntity getUserById(Integer id) {
        return userDao.findById(id);
    }
}

dao design

dao is a data access object, which is mainly used to realize the basic query function of database. It is implemented in the pattern of interface design.

Such as query function

First create an entity class object UserEntity

public class UserEntity {
    private Integer id;
    private String username;
    private String password;
    private String gender;
    private Date birthday;
    private int delete;

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getUsername() {
        return username;
    }

    public void setUsername(String username) {
        this.username = username;
    }

    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }

    public String getGender() {
        return gender;
    }

    public void setGender(String gender) {
        this.gender = gender;
    }

    public Date getBirthday() {
        return birthday;
    }

    public void setBirthday(Date birthday) {
        this.birthday = birthday;
    }

    public int getDelete() {
        return delete;
    }

    public void setDelete(int delete) {
        this.delete = delete;
    }

    @Override
    public String toString() {
        return "UserEntity{" +
                "id=" + id +
                ", username='" + username + '\'' +
                ", password='" + password + '\'' +
                ", gender='" + gender + '\'' +
                ", birthday=" + birthday +
                ", delete=" + delete +
                '}';
    }
}

UserDao interface

public interface UserDao {
    /**
     * Query data by ID
     * @param id    /
     * @return  /
     */
    UserEntity findById(Integer id);
}

UserDaoImpl implementation class

public class UserDaoImpl implements UserDao {
    @Override
    public UserEntity findById(Integer id) {
        Connection connection = JdbcUtil.getConnection();
        if (connection != null) {
            PreparedStatement statement = null;
            try {
                String sql = "select * from user where id=?";
                statement = connection.prepareStatement(sql);
                statement.setInt(1, id);
                ResultSet set = statement.executeQuery();
                while (set.next()) {
                    return UserEntityMapper.mapper(set);
                }
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        return null;
    }
}