JDBC 访问数据库的标准规范,用于执行SQL语句的API
增删改
/*
JDBC规范定义驱动接口 : java.sql.Driver
Mysql驱动包提供了实现类 : com.mysql.jdbc.Driver
*/
public class JDBCDemo01{
public static void main(String[] args) throw ClassNotFoundException{
// 1.注册驱动
// forName 方法执行将类进行初始化
Class.forName("com.mysql.jdbc.Driver");
// 2、获取连接 url,用户名、密码
String url = "jdbc:mysql://localhost:3306/db4";
connection con = DriverManager.getConnection(url,"root","12345");
// 3、获取 statement 对象
Statement statement = con.createStatement();
// 4、执行创建表操作
String sql = "create table test01(id int, name varhar(20),age int);";
// 5、增删改操作 使用executeUpdate(sql);
int i = statement.executeUpdate(sql);
// 6、返回值是受影响的函数
System.out.println(i);
// 7、关闭流
statement.close();
con.close();
}
}
获取查询语句+获取结果
public class JDBCDemo01{
public static void main(String[] args) throw SQLException{
//1、 注册驱动 可以省略
//2、获取连接
String url = "jdbc:mysql://localhost:3306/db4";
Connection con = DriverManager.getConnection(url,"root","123456")
// 3.获取Statement 对象
Statement statement = con.createStatement();
String sql = "select * from jdbc_user";
// 执行查询操作,返回的是一个ResultSet 结果对象
ResultSet resultSet = statement.executeQuery(sql);
// 4、处理结果集 resultSet
while(resultSet.next()){
// 获取 id
int id = resultSet.getInt("id");
// 获取姓名
String username = resultSet.getStrinng("username");
// 获取生日
Date birthday = resultSet.getDate("birthday");
System.out.println(id + "=" + username + ":" + birthday);
// 关闭连接
resultSet.close();
statement.close();
con.close();
}
}
释放资源
public class JDBCDemo05 {
public static void main(String[] args) {
Connection connection = null;
Statement statement = null;
ResultSet resultSet = null;
try {
//1.注册驱动(省略)
//2.获取连接
String url = "jdbc:mysql://localhost:3306/db4";
connection = DriverManager.getConnection(url, "root", "123456");
//3.获取 Statement对象
statement = connection.createStatement();
String sql = "select * from jdbc_user";
resultSet = statement.executeQuery(sql);
} catch (SQLException e) {
e.printStackTrace();
} finally {
/**
* 开启顺序: connection ==> statement => resultSet
* 关闭顺序: resultSet ==> statement ==> connection
*/
try {
connection.close();
resultSet.close();
statement.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
JDBC工具类封装
- 一个功能经常会被用到,进行封装成一个类,以便不同地方重用
- 可以封装获取链接对象的方法,从而达到复用的效果
public class JDBCutils{
//1. 定义字符串常量, 记录获取连接所需要的信息
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 = "123456";
static{
try{
// 注册驱动
Class.forName(DRIVERNAME);
}catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
//3.获取连接的静态方法
public static Connection getConnection(){
try {
//获取连接对象
Connection connection = DriverManager.getConnection(URL, USER, PASSWORD);
//返回连接对象
return connection;
} catch (SQLException e) {
e.printStackTrace();
return null;
}
}
//关闭资源的方法
public static void close(Connection con, Statement st){
if(con != null && st != null){
try {
st.close();
con.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
public static void close(Connection con, Statement st, ResultSet rs){
if(rs != null){
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
close(con,st);
}
}
JDBCUtils工具类实现数据插入
public void testInsert() throws SQLException {
//1.通过工具类获取连接
Connection connection = JDBCUtils.getConnection();
//2.获取Statement
Statement statement = connection.createStatement();
//3.编写SQL
String sql = "insert into jdbc_user values(null,'张百万','123','2020/1/1')";
//4 执行Sql
int i = statement.executeUpdate(sql);
System.out.println(i);
//5.关闭流
JDBCUtils.close(connection,statement);
预处理对象
// PreparedStatement ps = connection.prepareStatement(sql)
public class TestLogin02 {
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.获取 PrepareStatement 预编译对象
//4.1 编写SQL 使用 ? 占位符方式
String sql = "select * from jdbc_user where username = ? and password = ?";
PreparedStatement ps = connection.prepareStatement(sql);
//4.2 设置占位符参数
ps.setString(1,name);
ps.setString(2,pass);
//5. 执行查询 处理结果集
ResultSet resultSet = ps.executeQuery();
if(resultSet.next()){
System.out.println("登录成功! 欢迎您: " + name);
}else{
System.out.println("登录失败!");
}
//6.释放资源
JDBCUtils.close(connection,statement,resultSet);
}
}
public class TestPS {
public static void main(String[] args) throws SQLException {
Connection con = JDBCUtils.getConnection();
//获取 Sql语句执行对象
Statement st = con.createStatement();
//插入两条数据
st.executeUpdate("insert into jdbc_user values(null,'张三','123','1992/12/26')");
st.executeUpdate("insert into jdbc_user values(null,'李四','123','1992/12/26')");
//获取预处理对象
PreparedStatement ps = con.prepareStatement("insert into jdbc_user values(?,?,?,?)");
//第一条数 设置占位符对应的参数
ps.setString(1,null);
ps.setString(2,"长海");
ps.setString(3,"qwer");
ps.setString(4,"1990/1/10");
//执行插入
ps.executeUpdate();
//第二条数据
ps.setString(1,null);
ps.setString(2,"小斌");
ps.setString(3,"1122");
ps.setString(4,"1990/1/10");
//执行插入
ps.executeUpdate();
//释放资源
st.close();
ps.close();
con.close();
}
}
Statement 与 PreparedStatement的区别
- Statement用于执行静态SQL语句,在执行时,必须指定一个事先准备好的SQL语
- PrepareStatement预编译SQL语句对象,语句中可以包含动态参数“?”,在执行时可以为“?”动态设置参数值
- PrepareStatement可以减少编译次数提高数据库性能
JDBC 控制事务
- 获取连接
- 开启事务
- 获取到 PreparedStatement , 执行两次更新操作
- 正常情况下提交事务
- 出现异常回滚事务
- 最后关闭资源
public class JDBCTransaction {
//JDBC 操作事务
public static void main(String[] args) {
Connection con = null;
PreparedStatement ps = null;
try {
//1. 获取连接
con = JDBCUtils.getConnection();
//2. 开启事务
con.setAutoCommit(false);
//3. 获取到 PreparedStatement 执行两次更新操作
//3.1 tom 账户 -500
ps = con.prepareStatement("update account set money = money - ? where name = ? ");
ps.setDouble(1,500.0);
ps.setString(2,"tom");
ps.executeUpdate();
//模拟tom转账后 出现异常
System.out.println(1 / 0);
//3.2 jack 账户 +500
ps = con.prepareStatement("update account set money = money + ? where name = ? ");
ps.setDouble(1,500.0);
ps.setString(2,"jack");
ps.executeUpdate();
//4. 正常情况下提交事务
con.commit();
System.out.println("转账成功!");
} catch (SQLException e) {
e.printStackTrace();
try {
//5. 出现异常回滚事务
con.rollback();
} catch (SQLException ex) {
ex.printStackTrace();
}
} finally {
//6. 最后关闭资源
JDBCUtils.close(con,ps);
}
}
}