JDBC
概念
- JDBC:sun公司定义的一套操作数据库的规范,就是接口
- 驱动:JDBC接口的实现类
开发步骤
- 导入驱动jar包 mysql-connector-java-5.1.37-bin.jar
1.复制mysql-connector-java-5.1.37-bin.jar到项目的libs目录下
2.右键–>Add As Library - 注册驱动
- 获取数据库连接对象 Connection
- 定义sql
- 获取执行sql语句的对象 Statement
- 执行sql,接受返回结果
- 处理结果
- 释放资源
相关类的说明
- Driver’Manager:Connection connection = DriverManager.getConnection(“jdbc:mysql://localhost:3306/test”, “root”, “root”);//jdbc表示协议名mysql表示连接的数据库类型
- Connection:3个功能1.获取Statement 2.PrepareStatement 3.管理事物
- Statement:1.查询executeQuery(SQL).2.增删改executeUpdate(增删改SQL)
- PrepareStatement:获取方式connection.prepareStatement(“SQL”),给占位符赋值的方法setObject(1,值);查询executeQuery(),增删改executeUpdate();
代码
public static void main(String[] args) throws Exception {
Connection con = JDBCUtils.getConnection();
PreparedStatement ps = con.prepareStatement("update student set name=?,age=? where id=5");
ps.setString(1, "luna");
ps.setInt(2, 22);
int num = ps.executeUpdate();
System.out.println("共"+num+"行受影响");
JDBCUtils.release(con,ps);
}
public static void main(String[] args) throws Exception {
Connection con = JDBCUtils.getConnection();
PreparedStatement ps = con.prepareStatement("insert into student (name,age)values (?,?)");
ps.setString(1, "luna");
ps.setInt(2, 22);
int num = ps.executeUpdate();
System.out.println("共"+num+"行受影响");
JDBCUtils.release(con,ps);
}
Statement和PrepareStatement的区别
- 1.PrepareStatement速度快
- 2.PrepareStatement更安全,能防止SQL注入
JDBC工具类
- 书写步骤:构造方法私有化 ,静态方法,为了方便调用
- 功能:获取连接,释放资源
- 优化思想:1.驱动只需要加载一次,所以放在静态代码块中。2.数据库的连接信息配置在java中耦合性太强,所以将配置信息单独放到配置文件中,在静态代码块中读取配置信息
- coding
package Utils;
import java.io.IOException;
import java.sql.*;
import java.util.Properties;
public class JDBCUtils {
private JDBCUtils(){}
private static String url;
private static String user;
private static String password;
private static String driver;
static {
try {
Properties properties = new Properties();
properties.load(JDBCUtils.class.getClassLoader().getResourceAsStream("jdbc.properties"));
url = properties.getProperty("url");
user = properties.getProperty("user");
password = properties.getProperty("password");
driver = properties.getProperty("driver");
Class.forName(driver);
} catch (IOException e) {
e.printStackTrace();
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
public static Connection getConnection(){
Connection connection = null;
try {
connection = DriverManager.getConnection(url,user,password);
} catch (SQLException e) {
e.printStackTrace();
}
return connection;
}
public static void release(Connection connection, Statement statement){
if(connection!=null){
try {
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(statement!=null){
try {
statement.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
public static void release(Connection connection, Statement statement, ResultSet resultSet){
if(resultSet!=null){
try {
resultSet.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(connection!=null){
try {
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(statement!=null){
try {
statement.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
JDBC 管理事务
- 开启事务:setAutoCommit(boolean autoCommit) :调用该方法设置参数为false,即开启事务
* 在执行sql之前开启事务 - 提交事务:commit()
* 当所有sql都执行完提交事务 - 回滚事务:rollback()
* 在catch中回滚事务