1.JDBC的使用
JDBC 就是使用java语言操作关系型数据库的一套API.
1.创建工程,导入jar包.
MySQL :: Download MySQL Connector/J (Archived Versions)
2.注册驱动
Class.forName("com.mysql.jdbc.Driver");
3.获取连接
String url = "jdbc:mysql://localhost:3306/db2";
String username = "root";
String password = "1234";
Connection conn = DriverManager.getConnection(url, username, password);
4.定义sql语句
String sql = " UPDATE account set money = 9999 WHERE id = 1; ";
5.获取执行sql对象
Statement stat = conn.createStatement();
6.执行sql,返回值是受影响的代码行数
int count = stat.executeUpdate(sql);
7.处理返回结果
System.out.println(count);
8.释放资源
stat.close();
conn.close();
整体代码:
package com.yrh;
/*
* JDBC入门
*/
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
public class jdbc {
public static void main(String[] args) throws Exception{
// 1. 注册驱动(可省略)
//Class.forName("com.mysql.jdbc.Driver");
// 2. 获取连接
String url = "jdbc:mysql://localhost:3306/db2";
String username = "root";
String password = "1234";
Connection conn = DriverManager.getConnection(url, username, password);
// 3. 定义sql语句
String sql = " UPDATE account set money = 9999 WHERE id = 1; ";
// 4. 获取执行sql的对象 Statement
Statement stat = conn.createStatement();
// 5. 执行sql
int count = stat.executeUpdate(sql);
// 6. 处理结果
System.out.println(count);
// 7. 释放资源
stat.close();
conn.close();
}
}
2.JDBC中的事务处理
package com.yrh;
/*
* Connection
*/
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
public class jdbc_2 {
public static void main(String[] args) throws Exception{
// 1. 注册驱动
//Class.forName("com.mysql.jdbc.Driver");
// 2. 获取连接 useSSL=false 禁用安全连接
String url = "jdbc:mysql://localhost:3306/db2?useSSL=false";
String username = "root";
String password = "1234";
Connection conn = DriverManager.getConnection(url, username, password);
// 3. 定义sql语句
String sql1 = " UPDATE account set money = 9999 WHERE id = 1; ";
String sql2 = " UPDATE account set money = 9999 WHERE id = 2; ";
// 4. 获取执行sql的对象 Statement
Statement stat = conn.createStatement();
try {
// 开启事务
conn.setAutoCommit(false);
// 5. 执行sql
int count1 = stat.executeUpdate(sql1);
int count2 = stat.executeUpdate(sql2);
// 6. 处理结果
System.out.println(count1);
System.out.println(count2);
// 提交事务
conn.commit();
} catch (Exception e) {
// 回滚事务
conn.rollback();
e.printStackTrace();
}
// 7. 释放资源
stat.close();
conn.close();
}
}
3.讲数据库中的数据传入Java对象
package com.yrh;
/*
* JDBC statement
*/
import com.pojo.Account;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
public class jdbc_statement {
public static void main(String[] args) throws Exception{
// 1. 注册驱动
//Class.forName("com.mysql.jdbc.Driver");
// 2. 获取连接 useSSL=false 禁用安全连接
String url = "jdbc:mysql://localhost:3306/db2?useSSL=false";
String username = "root";
String password = "1234";
Connection conn = DriverManager.getConnection(url, username, password);
// 3. 定义sql语句
String sql = " select * from account";
// 4. 获取执行sql的对象 Statement
Statement stat = conn.createStatement();
// 5. 执行sql
ResultSet rs = stat.executeQuery(sql);
List<Account> list = new ArrayList<Account>();
while(rs.next()){
Account account = new Account();
int id = rs.getInt("id");
String name = rs.getString("name");
double money = rs.getDouble("money");
//给对象赋值
account.setId(id);
account.setName(name);
account.setMoney(money);
list.add(account);
}
System.out.println(list);
// 6. 处理结果
rs.close();
// 7. 释放资源
stat.close();
conn.close();
}
}
4.使用PrepareStatement防止sql注入的问题实现模拟登录
package com.yrh;
/*
* JDBC Preparestatement 放置sql注入
*/
import com.pojo.Account;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
public class jdbc_prepareStatement {
public static void main(String[] args) throws Exception{
// 1. 注册驱动
//Class.forName("com.mysql.jdbc.Driver");
// 2. 获取连接 useSSL=false 禁用安全连接
String url = "jdbc:mysql://localhost:3306/db2?useSSL=false";
String username = "root";
String password = "1234";
Connection conn = DriverManager.getConnection(url, username, password);
String name = "王伟";
String pwd = "qfewq";
String sql = " select * from tb_user where username = ? and password = ?";
// 4. 获取执行sql的对象 Statement
PreparedStatement pstmt = conn.prepareStatement(sql);
// 设置?的值
pstmt.setString(1,name);
pstmt.setString(2,pwd);
ResultSet rs = pstmt.executeQuery();
if(rs.next()){
System.out.println("登录成功");
}else{
System.out.println("登录失败");
}
// 6. 处理结果
rs.close();
// 7. 释放资源
pstmt.close();
conn.close();
}
}
5.数据库连接池
数据库连接池是一个容器, 负责分配管理数据库连接;
允许应用程序重复使用一个现有的数据库连接,而不是再重新建立一个;
数据库连接池标准接口:DataSource
获取连接:Connection getConnection()
Druid(德鲁伊):Druid连接池是阿里巴巴开源的数据库连接池项目.
配置文件信息:
driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql:///db2?useSSL=false&useServerPreStmts=true
username=root
password=1234
# 初始化连接数量
initialSize=5
# 最大连接数
maxActive=10
# 最大等待时间
maxWait=3000
连接池代码示例:
package com.yrh.jdbc;
import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.pool.DruidDataSourceFactory;
import javax.sql.DataSource;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.sql.Connection;
import java.util.Properties;
public class DruidDemo {
public static void main(String[] args) throws Exception {
// 1. 导入jar包
// 2. 定义配置文件
// 3. 加载配置代码
Properties prop = new Properties();
prop.load(new FileInputStream("JDBC/src/druid.properties"));
// 4. 获取连接对象
DataSource dataSource = DruidDataSourceFactory.createDataSource(prop);
// 5. 获取数据库连接
Connection conn = dataSource.getConnection();
System.out.println(conn);
//System.out.println(System.getProperty("user.dir"));
}
}