JAVA JDBC 数据库操作

使用JDBC进行增删改查

import java.sql.*;

public class JdbcDemo {
    private final static String SQLURL="jdbc:mysql://localhost:3306/test?useSSL=false&characterEncoding=utf8&serverTimezone=UTC";
    private final static String SQLUSER="root";
    private final static String SQLPAS="root";

    ResultSet select() throws SQLException {
        try(Connection connection= getConnection()){
            try(PreparedStatement ps=connection.prepareStatement("select * from table_name  where field=?")){
                ps.setObject(1,"查新条件");
                try(ResultSet rs= ps.executeQuery()){
                    return rs;
                }
            }
        }
    }
    //修改记录，返回受影响行数
    int update() throws SQLException {
        try(Connection connection= getConnection()) {
            try (PreparedStatement ps = connection.prepareStatement("update table_name  set field=? where field=?")) {
                ps.setObject(1, "条件值");
                ps.setObject(2, "修改新值");
                return ps.executeUpdate();
            }
        }
    }
    //添加一条记录，返回自增ID
    int insert() throws SQLException {
        try(Connection connection= getConnection()){
            try(PreparedStatement ps=connection.prepareStatement("insert into table_name (field,field2) values(?,?)",Statement.RETURN_GENERATED_KEYS)){
                ps.setObject(1,"字段1值");
                ps.setObject(2,"字段2值");
                return ps.executeUpdate();
            }
        }
    }
    //删除记录，返回受影响行数
    int delete() throws SQLException {
        try(Connection connection= getConnection()) {
            try (PreparedStatement ps = connection.prepareStatement("delete table_name  where field=?")) {
                ps.setObject(1, "条件值");
                return ps.executeUpdate();
            }
        }
    }
   static Connection getConnection() throws SQLException {
     return   DriverManager.getConnection(SQLURL,SQLUSER,SQLPAS);
    }
}

 事务：

数据库事务具有CAID特性：

Atomicity:原子性

Consistency:一致性

Isolation:隔离性

Durability:持久性

 

	脏读(Dirty Read)	非重复读(Non repeatable Read)	幻读(Phantom Read)
Read Uncommitted	Y	Y	Y
Read Committed		Y	Y
Repeatable Read			Y
Serializable

脏读(Dirty Read)：

事务A进行数据的更新，还没有提交之前，事务B进行了查询，读到的数据是事务A没有提交的数据，如果此时事务A进行回滚，那么事务B得到的数据就是脏的。

 

 

 

 

非重复读(Non repeatable Read)：

事务B中有两次查询，第一次查询是在事务A提交之前查询，第二次查询是在事务A提交之后查询，两次查询结果不一致。

 

幻读(Phantom Read)： 

事务B中前两条查询都没有查到ID为99的数据，但是在update之后查到了ID为99的数据。

 使用事务执行代码

import java.sql.*;

public class JdbcDemo {
    private final static String SQLURL="jdbc:mysql://localhost:3306/test?useSSL=false&characterEncoding=utf8&serverTimezone=UTC";
    private final static String SQLUSER="root";
    private final static String SQLPAS="root";
    //事务提交
    void transaction() throws SQLException {
        try(Connection connection= getConnection()) {
            connection.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
            connection.setAutoCommit(false);//关闭自动提交
            try {
                PreparedStatement ps = connection.prepareStatement("delete table_name  where field=?");
                ps.setObject(1, "条件值1");
                ps.executeUpdate();
                ps = connection.prepareStatement("delete table_name  where field=?");
                ps.setObject(1, "条件值2");
                ps.executeUpdate();
                connection.commit();//提交事务
            }catch (Exception ex){
                connection.rollback();
            }
            connection.setAutoCommit(true);//开启自动提交
        }
    }
   static Connection getConnection() throws SQLException {
     return   DriverManager.getConnection(SQLURL,SQLUSER,SQLPAS);
    }
}

 使用连接池

首先需要在pox.xml中添加第三方引用

<dependency>
　　<groupId>com.zaxxer</groupId>
   <artifactId>HikariCP</artifactId>
   <version>3.4.3</version>
</dependency>

import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;

import javax.sql.DataSource;
import java.sql.*;

public class JdbcDemo {
    private final static String SQLURL="jdbc:mysql://localhost:3306/test?useSSL=false&characterEncoding=utf8&serverTimezone=UTC";
    private final static String SQLUSER="root";
    private final static String SQLPAS="root";





    ResultSet select() throws SQLException {
        try(Connection connection= createDataSource().getConnection()){
            try(PreparedStatement ps=connection.prepareStatement("select * from table_name  where field=?")){
                ps.setObject(1,"查新条件");
                try(ResultSet rs= ps.executeQuery()){
                    return rs;
                }
            }
        }
    }
   static DataSource createDataSource(){
        HikariConfig hikariConfig= new HikariConfig();
        hikariConfig.setJdbcUrl(SQLURL);
        hikariConfig.setUsername(SQLUSER);
        hikariConfig.setPassword(SQLPAS);
        hikariConfig.setConnectionTimeout(1000);//设置数据库连接超时时间
        hikariConfig.setIdleTimeout(6000);//设置空闲时间
        hikariConfig.setMaximumPoolSize(10);//设置最大连接数
        return  new HikariDataSource(hikariConfig);
    }

}