JDBC基础
JDBC
JDBC, 全称为Java DataBase Connectivity standard, 它是一个面向对象的应用程序接口(API), 通过它可访问各类关系数据库。JDBC也是java核心类库的一部分
执行流程
实践
依赖
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.29</version>
</dependency>
PreparedStatement
- PreparedStatement通过把用户非法输入的单引号用\反斜杠做了转义，防止sql注入
- PreparedStatement需要set的方法，把？做替换
public static void main(String[] args) throws SQLException, ClassNotFoundException {
//1、注册mysql驱动
Class.forName("com.mysql.jdbc.Driver");
String url = "jdbc:mysql://localhost:3306/zg_test?useUnicode=true&characterEncoding=UTF8&zeroDateTimeBehavior=convertToNull&serverTimezone=Asia/Shanghai";
String username = "root";
String password = "123456";
//通过DriverManager获取Connection
Connection connection = DriverManager.getConnection(url, username, password);
String name = "test";
int age = 1;
//把用户非法输入的单引号用\反斜杠做了转义
PreparedStatement preparedStatement = connection.prepareStatement("select * from user where name = ? and age = ?");
//防止sql注入
preparedStatement.setString(1, name);
preparedStatement.setInt(2, age);
//执行sql获取数据
ResultSet resultSet = preparedStatement.executeQuery();
while (resultSet.next()) {
ResultSetMetaData metaData = resultSet.getMetaData();
int columnCount = metaData.getColumnCount();
for (int i = 1; i <= columnCount; i++) {
String columnName = metaData.getColumnName(i);
String data = resultSet.getString(i);
System.out.print(columnName + " : " + data + "\t");
}
System.out.println();
}
resultSet.close();
preparedStatement.close();
connection.close();
}
Statement(StatementImpl)
Statement statement = connection.createStatement();
用以上方式获取的Statement不可以防止sql注入
在执行sql时候也没有使用占位符。
public static void main(String[] args) throws ClassNotFoundException, SQLException {
Class.forName("com.mysql.jdbc.Driver");
String url = "jdbc:mysql://localhost:3306/zg_test?useUnicode=true&characterEncoding=UTF8&zeroDateTimeBehavior=convertToNull&serverTimezone=Asia/Shanghai";
String username = "root";
String password = "123456";
Connection connection = DriverManager.getConnection(url, username, password);
Statement statement = connection.createStatement();
String name = "test";
String age = "1";
ResultSet resultSet = statement.executeQuery(String.format("select * from user where name = '%s' and age = %s", name, age));
while (resultSet.next()) {
ResultSetMetaData metaData = resultSet.getMetaData();
int columnCount = metaData.getColumnCount();
for (int i = 1; i <= columnCount; i++) {
String columnName = metaData.getColumnName(i);
String data = resultSet.getString(i);
System.out.print(columnName + " : " + data + "\t");
}
System.out.println();
}
resultSet.close();
statement.close();
connection.close();
}
事务
在Connection里设置connection.setAutoCommit(false);
并且可以配置隔离级别connection.setTransactionIsolation(Connection.TRANSACTION_READ_UNCOMMITTED);
public static void main(String[] args) throws ClassNotFoundException, SQLException {
Class.forName("com.mysql.jdbc.Driver");
String url = "jdbc:mysql://localhost:3306/zg_test?useUnicode=true&characterEncoding=UTF8&zeroDateTimeBehavior=convertToNull&serverTimezone=Asia/Shanghai";
String username = "root";
String password = "123456";
Connection connection = DriverManager.getConnection(url, username, password);
//开启手动事务的关键是connection.setAutoCommit(false);
//jdbc默认事务是开启的，并且是自动提交
connection.setAutoCommit(false);
connection.setTransactionIsolation(Connection.TRANSACTION_READ_UNCOMMITTED);//设置隔离级别
PreparedStatement preparedStatement = connection.prepareStatement("insert into user(id,name,age) values (?,?,?)");
//防止sql注入
User user = new User();
user.setId(100);
user.setAge(12);
user.setName("jack1");
preparedStatement.setInt(1, user.getId());
preparedStatement.setString(2, user.getName());
preparedStatement.setInt(3, user.getAge());
//执行插入操作
try {
preparedStatement.executeUpdate();
int x = 1 / 0;//制造异常
connection.commit();//提交事务
} catch (Exception e) {
connection.rollback();//事务回滚
} finally {
preparedStatement.close();
connection.close();
}
}
Connection的隔离级别定义，这里的注释也比较重要。
/**
* A constant indicating that transactions are not supported.
*/
int TRANSACTION_NONE = 0;
/**
* A constant indicating that
* dirty reads, non-repeatable reads and phantom reads can occur.
* This level allows a row changed by one transaction to be read
* by another transaction before any changes in that row have been
* committed (a "dirty read"). If any of the changes are rolled back,
* the second transaction will have retrieved an invalid row.
*/
int TRANSACTION_READ_UNCOMMITTED = 1;
/**
* A constant indicating that
* dirty reads are prevented; non-repeatable reads and phantom
* reads can occur. This level only prohibits a transaction
* from reading a row with uncommitted changes in it.
*/
int TRANSACTION_READ_COMMITTED = 2;
/**
* A constant indicating that
* dirty reads and non-repeatable reads are prevented; phantom
* reads can occur. This level prohibits a transaction from
* reading a row with uncommitted changes in it, and it also
* prohibits the situation where one transaction reads a row,
* a second transaction alters the row, and the first transaction
* rereads the row, getting different values the second time
* (a "non-repeatable read").
*/
int TRANSACTION_REPEATABLE_READ = 4;
/**
* A constant indicating that
* dirty reads, non-repeatable reads and phantom reads are prevented.
* This level includes the prohibitions in
* <code>TRANSACTION_REPEATABLE_READ</code> and further prohibits the
* situation where one transaction reads all rows that satisfy
* a <code>WHERE</code> condition, a second transaction inserts a row that
* satisfies that <code>WHERE</code> condition, and the first transaction
* rereads for the same condition, retrieving the additional
* "phantom" row in the second read.
*/
int TRANSACTION_SERIALIZABLE = 8;
