JDBC概念：使用Java语言操作关系型数据库的一套api（可以用一套标准的jdbc操作所有类型的数据库，jdbc是接口，每一个数据库（mysql、oracle、db2...）都去实现jdbc的接口，每个数据库的实现类都不一样，这些实现类也称为驱动，例如，下载mysql驱动即jar包，就是下载mysql的实现类）
mysql驱动jar包
https://downloads.mysql.com/archives/c-j/下载第二个，完解压后 将”mysql-connector-java-5.1.48.jar“导入到idea
步骤：
1.注册驱动（idea不识别jar）(mysql 5之后的驱动包可以不写，在jar包META-INF\services\java.sql.Driver里有记录)
Class.forName("com.mysql.jdbc.Driver");-- Driver源码有静态代码块，加载registerDriver注册驱动
2.获取连接
Connnection conn = DriverManager.getConnection(url,username,password);
3.定义SQL语句
String sql = "updata ... ";
4.获取执行SQL对象
Statement stmt = conn.createStatement();
5.执行SQL（就是把sql语句发送给mysql，让mysql执行语句）
stmt.executeUpdate(sql);
6.处理返回结果
7.释放资源
package t1;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
public class jdbc {
public static void main(String[] args) throws ClassNotFoundException, SQLException {
Class.forName("com.mysql.jdbc.Driver");
String url = "jdbc:mysql://127.0.0.1:3306/db1?useSSL=false";
String username = "root";
String password = "1234";
Connection conn = DriverManager.getConnection(url,username,password);
String sql1 = "update account set money = 3000 where id = 1";
String sql2 = "update account set money = 3000 where id = 2";
Statement statement = conn.createStatement();
try {
conn.setAutoCommit(false);
int i1 = statement.executeUpdate(sql1);
System.out.println(i1);
int i2 = statement.executeUpdate(sql2);
System.out.println(i2);
conn.commit();
} catch (Exception throwables) {
conn.rollback();
throwables.printStackTrace();
}
statement.close();
conn.close();
}
}
语法：jdbc:mysql://ip地址(域名):端口号/数据库名称?参数键值对1&参数键值对2&参数键值对3...
示例：jdbc:mysql://127.0.0.1:3306/db1?useSSL=false
红色部分为协议即固定格式，后面为ip地址、端口号、连接哪个数据库,useSSL=false不提示红色警告
如果为默认地址，可以简写jdbc:mysql:///db1
JDBC API详解：
DriverManager：驱动管理类，作用：1.注册驱动，2.获取数据库连接
Connection：数据库连接对象，作用：1.获取执行SQL对象，2.管理事务
管理事务：注意此处指的是jdbc的事务管理，需要使用trycatch进行事务管理
开启事务：setAutoCommit(boolean autoCommit)：true为自动提交事务，false为手动提交事务（一般为false，如果用了true，后面就不用再写commit()了，会报错）
提交事务：commit()
回滚事务：rollback()
Statement：执行SQL语句，executeUpdate(sql)执行DML、DDL语句，executeQuery(sql)执行DQL语句（这个返回的是resultSet类型的值，下面会提到）
在执行DML时，会返回影响行数，若为0则没有修改到，另外需要注意最终结果需要反馈给用户，即需要做一个判断，若返回影响行数大于0，则打印 修改成功，反之 修改失败。
在执行DDL时，例如删除表时，返回的是0，在操作DDL时，只需要不报错即可
ResultSet：executeQuery(sql)执行DQL语句（比较常用）
while（rs.next()）{-- next方法是索引，自动往下判断是否有数据
rs.getXxx(参数)-- 参数可以是int也可以是字段名，int从左往右从1开始，Xxx为返回的数据类型
}
PreparedStatement：预编译sql语句并执行，用于防止sql注入（injection）的（防止别人输入特定的语句登录账号密码，进行破坏）
以后直接写preparedStatement，注意要传sql，并set参数，不用再写createStatement，下面有示例
作用：1.性能更快：预编译功能，2.防止sql注入：将敏感字符进行转义
原理：
预编译功能需要手动开启：useServerPrepStmts=true;（默认是关闭的）
查看：
将下面这段代码粘贴进mysql的安装路径里的my.ini（注意不能出现回车和中文双引号），并重启mysql服务
log-output=FILE
general-log=1
general_log_file="D:\mysql.log"
slow-query-log=1
slow_query_log_file="D:\mysql_slow.log"
long_query_time=2
粘贴完后，cmd打开命令符services.msc，重启mysql服务
关注D:\mysql.log文件内的变化
idea中输入
package t1;
import java.sql.*;
public class j1 {
public static void main(String[] args) throws SQLException {
String url = "jdbc:mysql:///db1?useSSL=false&useServerPrepStmts=true";
String username = "root";
String password = "1234";
Connection con = DriverManager.getConnection(url, username, password);
//Statement statement = con.createStatement();
String name = "123";
String pwd = "'or'1'='1";
String sql = "select * from tb_user where username = ? and password = ? ";
PreparedStatement ps = con.prepareStatement(sql);
ResultSet rs =null;
ps.setString(1,name);
ps.setString(2,pwd);
rs = ps.executeQuery();//注意此处不用再传sql语句
ps.setString(1,"name");
ps.setString(2,"pwd");
rs = ps.executeQuery();//注意此处不用再传sql语句
if (rs.next()){
System.out.println("登录成功");
}else {
System.out.println("登录失败");
}
con.close();
ps.close();
rs.close();
}
}
执行后日志的部分变化
2023-01-04T19:00:41.734079Z 6 Prepare select * from tb_user where username = ? and password = ?
2023-01-04T19:00:41.735577Z 6 Execute select * from tb_user where username = '123' and password = '\'or\'1\'=\'1'
2023-01-04T19:00:41.736250Z 6 Execute select * from tb_user where username = 'name' and password = 'pwd'
java代码运行后，mysql会进行sql语法检查，编译sql，这一个步骤很耗时，而预编译，根据日志来看，prepare已经把sql语句编译完了，只等输入？占位符。
在src下创建一个pojo文件夹，用来存放实体类javabean类，例如一些用户账户信息等
ResultSet的代码示例：
package t1;
import java.sql.*;
public class jdbc {
public static void main(String[] args) throws ClassNotFoundException, SQLException {
Class.forName("com.mysql.jdbc.Driver");
String url = "jdbc:mysql://127.0.0.1:3306/db1?useSSL=false";
String username = "root";
String password = "1234";
Connection conn = DriverManager.getConnection(url, username, password);
String sql = "select * from account";
Statement statement = conn.createStatement();
ResultSet rs = statement.executeQuery(sql);
while (rs.next()) {
int id = rs.getInt(1);
String name = rs.getString(2);
double money = rs.getDouble(3);
//int id = rs.getInt("id");
//String name = rs.getString("name");
//double money = rs.getDouble("money");
System.out.println(id);
System.out.println(name);
System.out.println(money);
System.out.println("------------");
}
conn.close();
rs.close();
statement.close();
}
}
将数据封装成对象并装入集合，传给前端
package t1;
import pojo.Account;
import java.sql.*;
import java.util.ArrayList;
public class jdbc {
public static void main(String[] args) throws ClassNotFoundException, SQLException {
Class.forName("com.mysql.jdbc.Driver");
String url = "jdbc:mysql://127.0.0.1:3306/db1?useSSL=false";
String username = "root";
String password = "1234";
Connection conn = DriverManager.getConnection(url, username, password);
String sql = "select * from account";
Statement statement = conn.createStatement();
ResultSet rs = statement.executeQuery(sql);
//将数据存入到集合中，用于传到前端。注意要创建pojo文件夹然后创建一个实现类，account用于封装数据
ArrayList<Account> ac = new ArrayList();
while (rs.next()) {
Account acc = new Account();
int id = rs.getInt(1);
String name = rs.getString(2);
double money = rs.getDouble(3);
//int id = rs.getInt("id");
//String name = rs.getString("name");
//double money = rs.getDouble("money");
acc.setId(id);
acc.setName(name);
acc.setMoney(money);
ac.add(acc);
}
System.out.println(ac);
conn.close();
rs.close();
statement.close();
}
}
sql注入示例：
package t1;
import java.sql.*;
public class j1 {
public static void main(String[] args) throws SQLException {
String url = "jdbc:mysql:///db1?useSSL=false";
String username = "root";
String password = "1234";
Connection con = DriverManager.getConnection(url, username, password);
Statement statement = con.createStatement();
String name = "123";
String pwd = "'or'1'='1";
String sql = "select * from tb_user where username = '"+name+"' and password = '"+pwd+"' ";
ResultSet rs = statement.executeQuery(sql);
if (rs.next()){
System.out.println("登录成功");
}else {
System.out.println("登录失败");
}
con.close();
statement.close();
rs.close();
}
}
防止sql注入：
package t1;
import java.sql.*;
public class j1 {
public static void main(String[] args) throws SQLException {
String url = "jdbc:mysql:///db1?useSSL=false";
String username = "root";
String password = "1234";
Connection con = DriverManager.getConnection(url, username, password);
//Statement statement = con.createStatement();
String name = "123";
String pwd = "'or'1'='1";
String sql = "select * from tb_user where username = ? and password = ? ";
PreparedStatement ps = con.prepareStatement(sql);
ps.setString(1,name);
ps.setString(2,pwd);
ResultSet rs = ps.executeQuery();//注意此处不用再传sql语句
if (rs.next()){
System.out.println("登录成功");
}else {
System.out.println("登录失败");
}
con.close();
ps.close();
rs.close();
}
}
数据库连接池：
数据库连接池类似于线程池，当有多个用户来访问数据库时，不用再次创建连接，如果有就可以直接使用，超出部分进行等待，当超过一定的等待时常，会自动进行判断之前的连接占用最长的，进行强制资源释放，给下一个用户
好处：1.资源重用，2.提升系统响应速度，3.避免数据库连接遗漏
Driud德鲁伊数据库连接池（属于阿里巴巴产品，可以在阿里云中查看）
1.导入jar包，粘贴properties
2.代码输入
Properties p = new Properties();
p.load(new FileInputStream("JDBC/src/druid.properties"));
DataSource dataSource = DruidDataSourceFactory.createDataSource(p);
Connection connection = dataSource.getConnection();
System.out.println(connection);
快捷键：alt+鼠标左键全选，可以同时整列进行编辑
在实体类中，建议使用基本数据类型对应的包装类
注意点：在查询时是调用resultSet的方法getString（int a），在设置时是调用preparedStatement的方法setString（int a，String b）
增删改查：
package t1;
import com.alibaba.druid.pool.DruidDataSourceFactory;
import org.junit.Test;
import pojo.Brand;
import javax.sql.DataSource;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.Properties;
public class t3 {
@Test
public void query() throws Exception {
Properties prop = new Properties();
prop.load(new FileInputStream("D:/Development/CordData/Project1/JDBC/src/druid.properties"));
DataSource dataSource = DruidDataSourceFactory.createDataSource(prop);
Connection connection = dataSource.getConnection();
String sql = "select * from tb_brand";
PreparedStatement p = connection.prepareStatement(sql);
ResultSet rs = p.executeQuery();
ArrayList<Brand> bs = new ArrayList<>();
while (rs.next()){
Brand b = new Brand();
int id = rs.getInt(1);
String brandName = rs.getString(2);
String companyName = rs.getString(3);
int ordered = rs.getInt(4);
String description = rs.getString(5);
int status = rs.getInt(6);
b.setId(id);
b.setBrandName(brandName);
b.setCompanyName(companyName);
b.setOrdered(ordered);
b.setDescription(description);
b.setStatus(status);
bs.add(b);
}
System.out.println(bs);
connection.close();
p.close();
rs.close();
}
@Test
public void add() throws Exception {
Properties prop = new Properties();
prop.load(new FileInputStream("D:/Development/CordData/Project1/JDBC/src/druid.properties"));
DataSource dataSource = DruidDataSourceFactory.createDataSource(prop);
Connection connection = dataSource.getConnection();
String sql = "insert into tb_brand (brand_name,company_name,ordered,description,status) values (?,?,?,?,?)";
PreparedStatement p = connection.prepareStatement(sql);
String br = "huang";
String c = "sss";
int o = 123;
String d = "qq";
int s = 0;
p.setString(1,br);
p.setString(2,c);
p.setInt(3,o);
p.setString(4,d);
p.setInt(5,s);
int rs = p.executeUpdate();
if (rs>0){
System.out.println("执行成功");
}else{
System.out.println("执行失败");
}
connection.close();
p.close();
}
@Test
public void updata() throws Exception {
Properties prop = new Properties();
prop.load(new FileInputStream("D:/Development/CordData/Project1/JDBC/src/druid.properties"));
DataSource dataSource = DruidDataSourceFactory.createDataSource(prop);
Connection connection = dataSource.getConnection();
String sql = "update tb_brand set brand_name=?,company_name=?,ordered=?,description=?,status=? where id =?";
PreparedStatement p = connection.prepareStatement(sql);
int id = 4;
String br = "香喷喷";
String c = "sss";
int o = 123;
String d = "qq";
int s = 0;
p.setString(1,br);
p.setString(2,c);
p.setInt(3,o);
p.setString(4,d);
p.setInt(5,s);
p.setInt(6,id);
int rs = p.executeUpdate();
if (rs>0){
System.out.println("执行成功");
}else{
System.out.println("执行失败");
}
connection.close();
p.close();
}
@Test
public void delete() throws Exception {
Properties prop = new Properties();
prop.load(new FileInputStream("D:/Development/CordData/Project1/JDBC/src/druid.properties"));
DataSource dataSource = DruidDataSourceFactory.createDataSource(prop);
Connection connection = dataSource.getConnection();
String sql = "delete from tb_brand where id =?";
PreparedStatement p = connection.prepareStatement(sql);
int id = 4;
p.setInt(1,id);
int rs = p.executeUpdate();
if (rs>0){
System.out.println("执行成功");
}else{
System.out.println("执行失败");
}
connection.close();
p.close();
}
}
184
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
