JDBC部分复习
回顾狂神JDB视频，整理一下，以便学习
JDBC：Java数据库连接 （java操作数据库的规范）
一、步骤
1.创建一个普通项目
2.导入数据库驱动
这里注意有一个坑：数据库驱动的版本问题
用5.0的版本时候，数据库驱动driver=com.mysql.jdbc.Driver；
而用8.0版本时，驱动driver=com.mysql.cj.jdbc.Driver
可以直接导入对应版本的jar包或者用maven导入
3.JDBC程序的一般步骤
- 加载驱动
- 用户信息和url
- 连接成功，返回一个数据库对象（用来操作数据库）Connection
- 执行SQL的对象PrepareStatement
- 可能存在结果集resultSet
- 关闭连接
二、初步学习-连接和测试
①先建一个表，插入数据
②IDEA连接数据库
具体步骤这里不赘述，注意可能需要改一下时区
③具体代码
package com.cj.jdbc;
import java.sql.*;
//我的第一个jdbc程序
public class JdbcFirstDemo {
public static void main(String[] args) throws ClassNotFoundException, SQLException {
String sql="select * from users";
new JdbcFirstDemo().jdbclodaing(sql);
}
public void jdbclodaing(String sql) throws ClassNotFoundException, SQLException {
//1.加载驱动
Class.forName("com.mysql.cj.jdbc.Driver");//固定写法，加载驱动
//2.用户信息和URL
String url ="jdbc:mysql://localhost:3306/jdbcstudy?serverTimezone=GMT%2B8&useUnicode=true&characterEncoding=utf8&useSSL=true";
String username="root";
String password="123456";
//3.连接成功，返回一个数据库与对象
Connection connection = DriverManager.getConnection(url, username, password);
//connection代表数据库 ，能做数据库做的事情，提交、回滚等
//4.执行SQL的对象Statement执行SQL的对象---增删改查
Statement statement = connection.createStatement();
//PreparedStatement preparedStatement = connection.prepareStatement();
//statement是执行sql的对象
//statement.executeQuery()//查询操作返回结果集
//statement.execute()//执行任何sql，效率低
//statement.executeUpdate()//更新、插入、删除都是用这个，返回一个受影响的行数---增删改
//5.执行SQL，可能存在结果，查看返回结果
ResultSet resultSet = statement.executeQuery(sql);//返回结果集，结果集中封装了我们全部的查询出来的结果
//resultset查询的结果集，封装了所有查询结果，只有查询操作有
while(resultSet.next()){
System.out.println("id="+resultSet.getObject("id"));
System.out.println("name="+resultSet.getObject("name"));
System.out.println("pwd="+resultSet.getObject("password"));
System.out.println("email="+resultSet.getObject("email"));
System.out.println("birth="+resultSet.getObject("birthday"));
//resultset.get...
//resultset.beforefirst指针移到最前
//resultset.afterlast指针移到最后
//resultset.absolute指定行
}
//6.释放连接
resultSet.close();
statement.close();
connection.close();
}
}
对象说明：
| 对象 | 作用 |
|---|---|
| Connection | 代表数据库对象，操作数据库相关 |
| Statement | 执行SQL的对象 |
| ResultSet | 返回的结果集 |
注意事项：释放连接的顺序
三、来一些基本的增删改查操作
1.由于JDBC连接代码几乎是固定不变的，我们可以抽象为一个工具类，进行调用即可
①首先来一个db.properties配置文件，作用：把相关配置信息放在这里，解耦清晰明了，直接调用即可
driver=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://localhost:3306/jdbcstudy?serverTimezone=GMT%2B8&useUnicode=true&characterEncoding=utf8&useSSL=true
username=root
password=123456
②工具类封装连接数据库的相关代码
package com.cj.utils;
import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.util.Collection;
import java.util.Properties;
/*工具类，读取配置文件
加载驱动
建立连接
释放资源
*/
public class JdbcUtil {
private static String driver=null;
private static String url=null;
private static String username=null;
private static String password=null;
static{
try{
InputStream resourceAsStream = JdbcUtil.class.getClassLoader().getResourceAsStream("db.properties");//获取具体的资源
Properties properties = new Properties();//读取信息到properties对象
properties.load(resourceAsStream);
driver = properties.getProperty("driver");
url = properties.getProperty("url");
username = properties.getProperty("username");
password = properties.getProperty("password");
//1.驱动只需要加载一次
Class.forName(driver);
} catch (Exception e) {
e.printStackTrace();
}
}
//2.获取连接
public static Connection getConnection() throws SQLException {
return DriverManager.getConnection(url,username,password);
}
//3.释放连接资源
public static void release(Connection connection, Statement statement, ResultSet resultSet) throws SQLException {
if(resultSet!=null)
resultSet.close();
if(statement!=null)
statement.close();
if(connection!=null)
connection.close();
}
}
③来测试基本的增删改查
package com.cj.jdbc;
import com.cj.utils.JdbcUtil;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
/*增删改查*/
public class JdbcSecondDemo {
Connection connection=null;
Statement statement=null;
ResultSet resultSet=null;
public static void main(String[] args) throws SQLException {
String sql="INSERT INTO `users`(id,NAME,PASSWORD,email,birthday) VALUES(5,'hucj','123456','zs@sina.com','1999-10-24')";
String sql1="delete from users where id='5'";
String sql2="update users set `name`='hcj' where id='3'" ;
String sql3="select `birthday` from users where id='2'" ;
// new JdbcSecondDemo().InsertToUsers(sql);
// new JdbcSecondDemo().deleteFromUsers(sql1);
// new JdbcSecondDemo().updateFromUsers(sql2);
new JdbcSecondDemo().searchFromUsers(sql3);
}
public void InsertToUsers(String sql) throws SQLException {
try{
connection = JdbcUtil.getConnection();//建立连接
statement=connection.createStatement();//拿到sql的执行对象
resultSet=statement.getResultSet();//查询语句返回的结果集
int i = statement.executeUpdate(sql);
if(i>0)
System.out.println("插入成功");
} catch (SQLException throwables) {
throwables.printStackTrace();
}
finally {
JdbcUtil.release(connection,statement,resultSet);
}
}
public void deleteFromUsers(String sql) throws SQLException {
try{
connection = JdbcUtil.getConnection();//建立连接
statement=connection.createStatement();//拿到sql的执行对象
resultSet=statement.getResultSet();//查询语句返回的结果集
int i = statement.executeUpdate(sql);
if(i>0)
System.out.println("删除成功");
} catch (SQLException throwables) {
throwables.printStackTrace();
}
finally {
JdbcUtil.release(connection,statement,resultSet);
}
}
public void updateFromUsers(String sql) throws SQLException {
try{
connection = JdbcUtil.getConnection();//建立连接
statement=connection.createStatement();//拿到sql的执行对象
resultSet=statement.getResultSet();//查询语句返回的结果集
int i = statement.executeUpdate(sql);
if(i>0)
System.out.println("修改成功");
} catch (SQLException throwables) {
throwables.printStackTrace();
}
finally {
JdbcUtil.release(connection,statement,resultSet);
}
}public void searchFromUsers(String sql) throws SQLException {
try{
connection = JdbcUtil.getConnection();//建立连接
statement=connection.createStatement();//拿到sql的执行对象
resultSet=statement.getResultSet();//查询语句返回的结果集
resultSet= statement.executeQuery(sql);
while(resultSet.next())
System.out.println("查询结果为："+resultSet.getObject("birthday"));
} catch (SQLException throwables) {
throwables.printStackTrace();
}
finally {
JdbcUtil.release(connection,statement,resultSet);
}
}
}
注：增删改成功的话返回的是受影响的行数
四、防止sql注入
本质：sql动态拼接
select * from users where name='name' and password ='password'
注： ''之间的是传入的参数
sql注入的情况
传入参数name=" 'or 1=1"; password=" 'or 1=1",则原sql语句变成
select * from users where name=' ' or 1=1 and password=" ' or 1=1";
PrepareStatement:预编译的statement,需要手动赋值
防止sql注入的原理：把传进来的数据都当作字符
package com.cj.jdbc;
import com.cj.utils.JdbcUtil;
import java.util.Date;
import java.sql.*;
/*
PreparedStament对象 ---防止sql注入，效率更好！！
*/
public class Jdbc3Demo {
Connection connection=null;
PreparedStatement preparedStatement=null;
ResultSet resultSet=null;
public static void main(String[] args) throws SQLException {
// new Jdbc3Demo().InsertIntoUser();
// new Jdbc3Demo().deleteFromUsers();
// new Jdbc3Demo().updateUsers();
new Jdbc3Demo().searchUsers();
}
public void InsertIntoUser() throws SQLException {
try{
connection = JdbcUtil.getConnection();
//区别
//使用?占位符代替参数
String sql="insert into users(`id`,`name`,`password`,`email`,`birthday`) values(?,?,?,?,?)";
preparedStatement=connection.prepareStatement(sql);//预编译sql，先写sql然后不执行
//手动给参数赋值
preparedStatement.setInt(1,7);
preparedStatement.setString(2,"James");
preparedStatement.setString(3,"1234567");
preparedStatement.setString(4,"939483874@qq.com");
preparedStatement.setDate(5,new java.sql.Date(new Date().getTime()));
//执行sql
int i = preparedStatement.executeUpdate();
if(i>0)
System.out.println("插入成功");
}
catch (SQLException e){
e.printStackTrace();
preparedStatement.close();
connection.close();
}
}
public void deleteFromUsers() throws SQLException {
try{
connection = JdbcUtil.getConnection();
String sql="delete from users where id=?";
preparedStatement = connection.prepareStatement(sql);
preparedStatement.setInt(1,6);
int i = preparedStatement.executeUpdate();
if(i>0)
System.out.println("删除成功");
} catch (SQLException throwables) {
throwables.printStackTrace();
preparedStatement.close();
connection.close();
}
}
public void updateUsers() throws SQLException {
try{
connection = JdbcUtil.getConnection();
String sql="update users set `name`=? where id=?";
preparedStatement = connection.prepareStatement(sql);
preparedStatement.setString(1,"James");
preparedStatement.setInt(2,3);
int i = preparedStatement.executeUpdate();
if(i>0)
System.out.println("更新成功");
} catch (SQLException throwables) {
throwables.printStackTrace();
preparedStatement.close();
connection.close();
}
}
public void searchUsers() throws SQLException {
try{
connection = JdbcUtil.getConnection();
String sql="select `name` from users where id=?";
preparedStatement = connection.prepareStatement(sql);
preparedStatement.setInt(1,3);
resultSet = preparedStatement.executeQuery();
while(resultSet.next())
System.out.println(resultSet.getObject(1));
} catch (SQLException throwables) {
throwables.printStackTrace();
preparedStatement.close();
connection.close();
}
}
}
五、JDBC事务
事务的ACID：原子性一致性隔离性持久性
Connection connection=null;
PreparedStatement preparedStatement=null;
ResultSet resultSet=null;
connection=JdbcUtil.getConnection();
try {
connection.setAutoCommit(false);//关闭数据库自动提交，而后自动会开启事务
String sql1="update account set money=money-100 where name='A'";
preparedStatement=connection.prepareStatement(sql1);
preparedStatement.executeUpdate();
int x=1/0;
String sql2="update account set money=money+100 where name='B'";
preparedStatement=connection.prepareStatement(sql2);
preparedStatement.executeUpdate();
connection.commit();//提交
} catch (SQLException throwables) {
throwables.printStackTrace();
connection.rollback();//不写也会默认回滚
}
finally {
JdbcUtil.release(connection,preparedStatement,resultSet);
}
六、数据库连接池
为什么要用数据库连接池？
如果没有数据库连接池：建立连接–》提供服务–》关闭连接
浪费资源
与线程池思想一致，池化思想，要用就拿，不用就放回
本质：实现DataSource接口
导包
c3p0
Java实现
ComboPooledDataSource comboPooledDataSource = new ComboPooledDataSource();//获取C3P0连接池
comboPooledDataSource.setUser("root");
comboPooledDataSource.setPassword("123456");
comboPooledDataSource.setJdbcUrl("jdbc:mysql://localhost:3306/jdbcstudy?serverTimezone=GMT%2B8&useUnicode=true&characterEncoding=utf8&useSSL=true");
comboPooledDataSource.setDriverClass("com.mysql.cj.jdbc.Driver");
Connection connection = comboPooledDataSource.getConnection();
Statement statement = connection.createStatement();
ResultSet resultSet = statement.executeQuery("select * from `users` ");
while(resultSet.next()){
System.out.println(resultSet.getString("NAME"));
}
XML实现具体后面Mybatis再做整理
DBCP
BasicDataSource basicDataSource=new BasicDataSource();
basicDataSource.setUsername("root");
basicDataSource.setPassword("123456");
basicDataSource.setUrl("jdbc:mysql://localhost:3306/jdbcstudy?serverTimezone=GMT%2B8&useUnicode=true&characterEncoding=utf8&useSSL=true");
basicDataSource.setDriverClassName("com.mysql.cj.jdbc.Driver");
Connection connection = basicDataSource.getConnection();
Statement statement = connection.createStatement();
ResultSet resultSet = statement.executeQuery("select * from users");
while(resultSet.next()){
System.out.println(resultSet.getString("NAME"));
}
Druid
换一种方式，配置文件
db1.properties
driver=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://localhost:3306/jdbcstudy?serverTimezone=GMT%2B8&useUnicode=true&characterEncoding=utf8&useSSL=true
username=root
password=123456
连接
Properties properties = new Properties();
properties.load(ClassLoader.getSystemClassLoader().getResourceAsStream("db1.properties"));//读取配置文件
DataSource dataSource = DruidDataSourceFactory.createDataSource(properties);
Connection connection = dataSource.getConnection();
Statement statement = connection.createStatement();
ResultSet resultSet = statement.executeQuery("select * from users");
while(resultSet.next()){
System.out.println(resultSet.getString("NAME"));
3974
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
