目录
方式二：:使用反射动态加载Driver类，增加灵活性，减少依赖性
方式一：属于静态加载，灵活性差，依赖性强
//1.注册驱动(import com.mysql.jdbc.Driver;)
Driver driver = new Driver();
//2.连接地址-本质上还是socket连接
/*解析
规定协议：jdbc:mysql://
连接主机或者ip地址：localhost
端口：3306
数据口：jdbc
*/
String url = "jdbc:mysql://localhost:3306/jdbc";
//3.将用户名和密码放入Properties对象中(import java.util.Properties;)
Properties properties = new Properties();
properties.setProperty("user","root");
properties.setProperty("password","123");
//4.得到connerct-进行连接
Connection connect = driver.connect(url, properties);
//5.写sql语句
String sql="insert into actor values(null,'刘德华','男','1970-11-11','110')";
String sql="update actor set name='张学友' where id=1 ";
String sql="delete from actor where id=1 ";
//6.statement 用于执行静态sql语句并返回其生成的结果对象
Statement statement = connect.createStatement();
//statement.executeUpdate(sql)执行dml语句并返回影响行数
int i = statement.executeUpdate(sql);
//判断是否成功
System.out.println( i>0? "成功":"失败");
//7.关闭资源
statement.close();
connect.close();方式二：:使用反射动态加载Driver类，增加灵活性，减少依赖性
Class<?> aClass = Class.forName("com.mysql.jdbc.Driver");
Driver driver =(Driver) aClass.newInstance();
//数据库路径
String url = "jdbc:mysql://localhost:3306/jdbc";
//3.将用户名和密码放入Properties对象中(import java.util.Properties;)
Properties properties = new Properties();
properties.setProperty("user","root");
properties.setProperty("password","123");
//4.得到connerct-进行连接
Connection connect = driver.connect(url, properties);
//5.写sql语句
String sql="insert into actor values(null,'刘德华2','男','1970-11-11','110')";
//6.statement 用于执行静态sql语句并返回其生成的结果对象
Statement statement = connect.createStatement();
//statement.executeUpdate(sql)执行dml语句并返回影响行数
int i = statement.executeUpdate(sql);
System.out.println( i>0? "成功":"失败");
//7.关闭资源
statement.close();
connect.close();方式三：DriverManager，增加灵活性，减少依赖性
Class<?> aClass = Class.forName("com.mysql.jdbc.Driver");
Driver driver =(Driver) aClass.newInstance();
String url = "jdbc:mysql://localhost:3306/jdbc";
String user = "root";
String password = "123";
//注册驱动
DriverManager.registerDriver(driver);
//进行连接
Connection connection = DriverManager.getConnection(url, user, password);
String sql="insert into actor values(null,'刘德华3','男','1970-11-11','110')";
//6.statement 用于执行静态sql语句并返回其生成的结果对象
Statement statement = connection.createStatement();
//statement.executeUpdate(sql)执行dml语句并返回影响行数
int i = statement.executeUpdate(sql);
System.out.println( i>0? "成功":"失败");
//7.关闭资源
statement.close();
connection.close();方式四：DriverManager底层自动完成注册驱动
Class<?> aClass = Class.forName("com.mysql.jdbc.Driver");
Driver driver =(Driver) aClass.newInstance();
String url = "jdbc:mysql://localhost:3306/jdbc";
String user = "root";
String password = "123";
Connection connection = DriverManager.getConnection(url, user, password);
//5.写sql语句
String sql="insert into actor values(null,'刘德华4','男','1970-11-11','110')";
//6.statement 用于执行静态sql语句并返回其生成的结果对象
Statement statement = connection.createStatement();
//statement.executeUpdate(sql)执行dml语句并返回影响行数
int i = statement.executeUpdate(sql);
System.out.println( i>0? "成功":"失败");
//7.关闭资源
statement.close();
connection.close();方式五：前四种都是写死的，对其进行配置文件降低强依赖
//通过properties对象获取配置文件中的数据
Properties properties = new Properties();
properties.load(new FileInputStream("src\\mysql.properties"));
//获取值
String driver = properties.getProperty("driver");
String url = properties.getProperty("url");
String user = properties.getProperty("user");
String password = properties.getProperty("password");
//动态注册驱动
Class.forName(driver);
//进行连接
Connection connection = DriverManager.getConnection(url, user, password);
//5.写sql语句
String sql="insert into actor values(null,'刘德华5','男','1970-11-11','110')";
//6.statement 用于执行静态sql语句并返回其生成的结果对象
Statement statement = connection.createStatement();
//statement.executeUpdate(sql)执行dml语句并返回影响行数
int i = statement.executeUpdate(sql);
System.out.println( i>0? "成功":"失败");
//7.关闭资源
statement.close();
connection.close();ResultSet结果集：得到查询的数据
读取数据
//通过properties对象获取配置文件中的数据
Properties properties = new Properties();
properties.load(new FileInputStream("src\\mysql.properties"));
//获取值
String driver = properties.getProperty("driver");
String url = properties.getProperty("url");
String user = properties.getProperty("user");
String password = properties.getProperty("password");
//动态注册驱动
Class.forName(driver);
Connection connection = DriverManager.getConnection(url, user, password);
//5.写sql语句
String sql="select * from actor ";
//6.statement 用于执行静态sql语句并返回其生成的结果对象
Statement statement = connection.createStatement();
//statement.executeQuery(sql)执行查询语句并返回影响行数
ResultSet resultSet = statement.executeQuery(sql);
//循环输出resultSet.next()会判断下一行数据是否存在，不存在返回false跳出循环
while (resultSet.next()){
int id = resultSet.getInt(1);//获取id字段数据
String name = resultSet.getString(2);//获取name字段数据
String sex = resultSet.getString(3);//获取sex字段数据
Date borndate = resultSet.getDate(4);//获取borndate字段数据
String phone = resultSet.getString(5);//获取phone字段数据
System.out.println(id +"\t"+ name +"\t"+ sex +"\t"+ borndate +"\t"+ phone);
}
//7.关闭资源
statement.close();
connection.close();
resultSet.close();防止sql注入
1：JDBC中存在的sql注入 statement 用于执行静态sql语句并返回其生成的结果对象，此对象会引发sql注入 PreparedStatement 进行预处理可以防止sql注入，它是statement的子接口
2：PreparedStatement为什么可以防止sql注入 PreparedStatement执行sql语句中参数字段用(?)表示，调用PreparedStatement对象中的set方法进行对占位符(?)进行赋值，set方法中有两个参数，参数1是第几个占位符，参数2是给占位符赋值的值
//通过properties对象获取配置文件中的数据
Properties properties = new Properties();
properties.load(new FileInputStream("src\\mysql.properties"));
//获取值
String driver = properties.getProperty("driver");
String url = properties.getProperty("url");
String user = properties.getProperty("user");
String password = properties.getProperty("password");
//动态注册驱动
Class.forName(driver);
Connection connection = DriverManager.getConnection(url, user, password);
//5.写sql语句-?相当于占位符
String sql="select * from actor where user = ? and password = ? ";
//6.Statement 用于执行静态sql语句并返回其生成的结果对象-会发生sql注入
//Statement statement = connection.createStatement();
//7.PreparedStatement 会防止sql注入
PreparedStatement preparedStatement = connection.prepareStatement(sql);
//进行？赋值 1号占位符，值
preparedStatement.setString(1,"root");
//进行？赋值 2号占位符，值
preparedStatement.setString(2,"123");
//statement.executeQuery(sql)执行查询语句并返回影响行数
ResultSet resultSet = preparedStatement.executeQuery();
while (resultSet.next()){
int id = resultSet.getInt(1);//获取id字段数据
String name = resultSet.getString(2);//获取name字段数据
String sex = resultSet.getString(3);//获取sex字段数据
Date borndate = resultSet.getDate(4);//获取borndate字段数据
String phone = resultSet.getString(5);//获取phone字段数据
System.out.println(id +"\t"+ name +"\t"+ sex +"\t"+ borndate +"\t"+ phone);
}
//7.关闭资源
preparedStatement.close();
connection.close();
resultSet.close();Jdbc封装Utils工具类
public class JdbcUtils {
private static String driver;
private static String url;
private static String user;
private static String password;
//获取配置中的数据
static {
try {
Properties properties = new Properties();
properties.load(new FileInputStream("src\\mysql.properties"));
driver = properties.getProperty("driver");
url = properties.getProperty("url");
user = properties.getProperty("user");
password = properties.getProperty("password");
} catch (IOException e) {
throw new RuntimeException(e);
}
}
//进行连接数据库
public static Connection getConnection(){
try {
return DriverManager.getConnection(url,user,password);
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
/*关闭资源
* 1：ResultSet
* 2: Statement或者PreparedStatement
* 3:Connection
* */
public static void close(ResultSet set, Statement statement, Connection connection){
try {
//判断连接不为空进行关闭
if(set != null){
set.close();
}
if(statement != null){
statement.close();
}
if(connection != null){
connection.close();
}
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
}测试类
public static void main(String[] args){
PreparedStatement preparedStatement = null;
//进行连接数据库
Connection connection = null;
String sql ="select * from actor where user = ? and password = ? ";
try {
connection = JdbcUtils.getConnection();
preparedStatement = connection.prepareStatement(sql);
//给占位符赋值
preparedStatement.setString(1,"root");
preparedStatement.setString(2,"123");
//执行
preparedStatement.executeUpdate();
} catch (SQLException e) {
//打印异常信息
e.printStackTrace();
}finally {
//关闭资源
JdbcUtils.close(null,preparedStatement,connection);
}
}JDBC事务
1：JDBC中connection对象被创建时，默认时自动提交事务，每次执行sql执行成功自动提交，不会回滚
2：JDBC中为了让多条sql语句作为一个整体执行，需要使用事务
3：调用connection的setAutoCommit(false) 可以取消自动提交，及开启事务
4;执行完所有sql后，调用connection的commit()进行提交
5：某个sql发生操作异常或失败，可以调用connection的rollback()进行回滚
1：演示不关闭自动提交，如果程序发生异常会导致，A账号-100，B账号未+100
public static void main(String[] args){
PreparedStatement preparedStatement = null;
//进行连接数据库
Connection connection = null;
String sql1 ="update account set balance = balance - ? where id =?";
String sql2 ="update account set balance = balance + ? where id =?";
try {
connection = JdbcUtils.getConnection();
connection.setAutoCommit(false);
preparedStatement = connection.prepareStatement(sql1);
//给占位符赋值
preparedStatement.setString(1,"100");
preparedStatement.setString(2,"1");
//执行
preparedStatement.executeUpdate();
int i = 1 / 0;//发生异常
preparedStatement = connection.prepareStatement(sql2);
//给占位符赋值
preparedStatement.setString(1,"100");
preparedStatement.setString(2,"2");
//执行
preparedStatement.executeUpdate();
} catch (SQLException e) {
//打印异常信息
e.printStackTrace();
}finally {
//关闭资源
JdbcUtils.close(null,preparedStatement,connection);
}
}2：演示关闭自动提交，只需要调用connection.setAutoCommit(false);
public static void main(String[] args){
PreparedStatement preparedStatement = null;
//进行连接数据库
Connection connection = null;
String sql1 ="update account set balance = balance - ? where id =?";
String sql2 ="update account set balance = balance + ? where id =?";
try {
connection = JdbcUtils.getConnection();
connection.setAutoCommit(false);//开启事务
preparedStatement = connection.prepareStatement(sql1);
//给占位符赋值
preparedStatement.setString(1,"100");
preparedStatement.setString(2,"1");
//执行
preparedStatement.executeUpdate();
int i = 1 / 0;//发生异常
preparedStatement = connection.prepareStatement(sql2);
//给占位符赋值
preparedStatement.setString(1,"100");
preparedStatement.setString(2,"2");
//执行
preparedStatement.executeUpdate();
//没有异常最终进行提交
connection.commit();
} catch (SQLException e) {
//默认回滚到事务开始的时候
try {
connection.rollback();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
//打印异常信息
e.printStackTrace();
}finally {
//关闭资源
JdbcUtils.close(null,preparedStatement,connection);
}
}JDBC-Druid德鲁伊下载及Utils
Druid下载
官方地址：https://repo1.maven.org/maven2/com/alibaba/druid/
下载：druid-1.1.10.jar
将jar包加入到程序中去
配置 druid.properties文件到src目录下
driverClassName=com.mysql.jdbc.Driver
#rewriteBatchedStatements=true 开启批量sql处理不要删除掉这条语句
url=jdbc:mysql://localhost:3306/jdbc?rewriteBatchedStatements=true
username=root
password=123
#初始化连接池数
initialSize=10
#最小连接数
minIdle=5
#最大连接数
maxActive=20
#最大等待超时时间5秒,以上就是当连接达到最大连接数时，进入等待队列最多等5秒，5秒后超时
maxWait=5000 类中创建Properties进行读取配置使用
类中创建Properties进行读取配置
public static void main(String[] args) throws Exception {
Properties properties = new Properties();
properties.load(new FileInputStream("src\\druid.properties"));
//创建一个指定参数的数据库连接池
DataSource dataSource = DruidDataSourceFactory.createDataSource(properties);
//进行连接
Connection connection = dataSource.getConnection();
}封装为Utils工具类
public class JdbcUtilsByDruid {
//定义数据库连接池
private static DataSource ds;
//获取配置中的数据,并进行初始化连接池
static {
Properties properties = new Properties();
try {
properties.load(new FileInputStream("src\\druid.properties"));
//指定参数的数据库连接池
ds = DruidDataSourceFactory.createDataSource(properties);
} catch (Exception e) {
e.printStackTrace();
}
}
//进行连接数据库
public static Connection getConnection() throws SQLException {
return ds.getConnection();
}
/*关闭资源-数据连接池中提到的close不是关闭资源的意思，而是用完放回数据池的意思
* 1：ResultSet
* 2: Statement或者PreparedStatement
* 3:Connection
* */
public static void close(ResultSet set, Statement statement, Connection connection){
try {
//判断连接不为空进行关闭
if(set != null){
set.close();
}
if(statement != null){
statement.close();
}
if(connection != null){
connection.close();
}
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
}测试类
public static void main(String[] args){
PreparedStatement preparedStatement = null;
ResultSet set = null;
//进行连接数据库
Connection connection = null;
//sql
String sql1 = "select * from students where id=?";
try {
connection = JdbcUtilsByDruid.getConnection();
//开启事务
connection.setAutoCommit(false);
preparedStatement = connection.prepareStatement(sql1);
//给占位符赋值
preparedStatement.setInt(1,1);
//执行
set = preparedStatement.executeQuery();
while (set.next()){
int id = set.getInt(1);//获取id字段数据
String name = set.getString(2);//获取name字段数据
String sex = set.getString(3);//获取sex字段数据
Date borndate = set.getDate(4);//获取borndate字段数据
String phone = set.getString(5);//获取phone字段数据
System.out.println(id +"\t"+ name +"\t"+ sex +"\t"+ borndate +"\t"+ phone);
}
//没有异常最终进行提交
connection.commit();
} catch (SQLException e) {
//默认回滚到事务开始的时候
try {
connection.rollback();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
//打印异常信息
e.printStackTrace();
}finally {
//关闭资源
JdbcUtilsByDruid.close(set,preparedStatement,connection);
}
}
565
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
