什么是JDBC
是一种访问数据库的规范（API、接口）。这种规范可以屏蔽不同数据库的实现细节，提供统一的访问数据库的方式。
JDBC语法
更新操作
public class JDBCDemo01 {
public static void main(String[] args) throws Exception{
//注册驱动（从JDBC3开始可以省略）
Class.forName("com.mysql.jdbc.Driver");
//获取连接
//URL-> 协议//主机IP:端口号/数据库（资源名）
String url = "jdbc:mysql://localhost:3306/db4?characterEncoding=UTF-8";
Connection connection = DriverManager.getConnection(url,
"root", "980909");
//获取语句执行平台对象
Statement statement = connection.createStatement();
//执行操作
String sql = "create table test(id int, name varchar(20), age int);";
int rows = statement.executeUpdate(sql);
System.out.println(rows);
//关闭流
statement.close();
connection.close();
}
}查询操作
public class JDBCDemo02 {
public static void main(String[] args) throws Exception{
Class.forName("com.mysql.jdbc.Driver");
String url = "jdbc:mysql://localhost:3306/db4?characterEncoding=UTF-8";
Connection connection = DriverManager.getConnection(url,
"root", "980909");
Statement statement = connection.createStatement();
String sql = "select * from jdbc_user;";
ResultSet resultSet = statement.executeQuery(sql);
while (resultSet.next()) {
int id = resultSet.getInt("id");
// int id = resultSet.getInt(1); 列号从1开始
String username = resultSet.getString("username");
String password = resultSet.getString("password");
Date birthday = resultSet.getDate("birthday");
System.out.println(id + " " + username + " " + password + " " + birthday);
}
resultSet.close();
statement.close();
connection.close();
}
}写一个简单的JDBC工具类
public class JDBCUtils {
public static final String DRIVERNAME = "com.mysql.jdbc.Driver";
public static final String URL = "jdbc:mysql://localhost:3306/db4?characterEncoding=UTF-8";
public static final String USER = "root";
public static final String PASSWORD = "980909";
static {
try {
Class.forName(DRIVERNAME);
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
public static Connection getConnection() {
try {
Connection connection = DriverManager.getConnection(URL, USER, PASSWORD);
return connection;
} catch (SQLException throwables) {
throwables.printStackTrace();
return null;
}
}
public static void close(Connection connection, Statement statement) {
if (statement != null) {
try {
connection.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
if (connection != null) {
try {
connection.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
public static void close(Connection connection, Statement statement, ResultSet resultSet) {
if (resultSet != null) {
try {
resultSet.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
if (statement != null) {
try {
connection.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
if (connection != null) {
try {
connection.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
}SQL注入
SELECT * FROM jdbc_user WHERE username = 'tom' AND PASSWORD = '1' OR 1=1;
JDBC例子
public class TestLogin01 {
public static void main(String[] args) throws SQLException {
//1.获取连接
Connection connection = JDBCUtils.getConnection();
//2.获取Statement
Statement statement = connection.createStatement();
//3.获取用户输入的用户名和密码
Scanner sc = new Scanner(System.in);
System.out.println("请输入用户名: ");
String name = sc.nextLine();
System.out.println("请输入密码: ");
String pass = sc.nextLine();
System.out.println(pass);
//4.拼接Sql,执行查询
String sql = "select * from jdbc_user " +
"where username = " + " '" + name + "' " + " and password = " + " '" + pass + "'";
System.out.println(sql);
ResultSet resultSet = statement.executeQuery(sql);
//5.处理结果集,判断结果集是否为空
if (resultSet.next()) {
System.out.println("登录成功! 欢迎您: " + name);
} else {
System.out.println("登录失败!");
}
//释放资源
JDBCUtils.close(connection, statement, resultSet);
}
}分析
如果此时用户的密码为 xxx' or '1' = '1, 就会被sql注入。
防止这种问题，就需要不进行字符串拼接。
解决办法
使用PreparedStatement
public class TestLogin02 {
public static void main(String[] args) throws Exception{
Scanner sc = new Scanner(System.in);
String str1 = sc.next();
String str2 = sc.next();
Connection connection = JDBCUtils.getConnection();
String sql = "select * from jdbc_user where username = ? and password = ?";
PreparedStatement preparedStatement = connection.prepareStatement(sql);
//如果要设置null就用setObject
preparedStatement.setString(1, str1);
preparedStatement.setString(2, str2);
ResultSet resultSet = preparedStatement.executeQuery();
if (resultSet.next()) {
System.out.println("查询成功");
} else {
System.out.println("查询失败");
}
JDBCUtils.close(connection, preparedStatement, resultSet);
}
}preparedStatement的性能也很好，sql的模板（就是带?的）会提前编译好，插入数据的时候性能高。
普通的statement插入数据的时候，插10000次会编译10000次。性能不好。
事务
public class TestTrans {
public static void main(String[] args) {
Connection connection = null;
PreparedStatement preparedStatement = null;
try {
connection = JDBCUtils.getConnection();
//关闭自动提交，相当于开启事务
connection.setAutoCommit(false);
String sql = "update account set money = money - ? where name = ?";
preparedStatement = connection.prepareStatement(sql);
preparedStatement.setDouble(1, 500.0);
preparedStatement.setString(2, "tom");
int rows = preparedStatement.executeUpdate();
String sql2 = "update account set money = money + ? where name = ?";
preparedStatement = connection.prepareStatement(sql);
preparedStatement.setDouble(1, 500.0);
preparedStatement.setString(2, "jack");
int rows2 = preparedStatement.executeUpdate();
System.out.println(rows + "\t" + rows2);
connection.commit();
} catch (SQLException throwables) {
throwables.printStackTrace();
try {
connection.rollback();
} catch (SQLException e) {
e.printStackTrace();
}
} finally {
JDBCUtils.close(connection, preparedStatement);
}
}
}
京公网安备 11010502036488号