文章目录
PreparedStatement预处理对象
sql注入的问题以解决方法(预处理对象)
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Scanner;
public class UserLogin {
public static void main(String[] args) throws SQLException {
//创建Scanner对象
final Scanner scanner = new Scanner(System.in);
//调用方法键盘录入要登录的用户名和密码
System.out.println("请你输入用户名");
String username = scanner.next();
System.out.println("请你输入密码");
String password = scanner.next();
//获取连接
Connection conn = JDBCUtils.getConn();
//获取执行平台
Statement statement = conn.createStatement();
//准备sql语句
//String sql = "select * from user where username = " + username + "and password =" + password;
String sql = "select * from user where username = '"+username+"' and password = '"+password+"'";
//执行sql语句
ResultSet resultSet = statement.executeQuery(sql);
if (resultSet.next()){
System.out.println("登录成功");
}else{
System.out.println("登录失败");
}
JDBCUtils.close(conn,statement,resultSet);
}
}
使用预处理对象(PreparedStatement)实现操作
1.概述:PreparedStatement接口,继承 Statement 接口
2.获取:Connection中的方法
PrepareStatemet prepareStatement(String sql) 获取执行平台
3.执行sql方法:
int executeUpdate()
ResultSet executeQuery()
4.注意:
PreparedStatement支持在sql语句中写?(占位符)
比如:select * from user where username = ? and passwoed = ?
5.为?赋值:
void setObject(int parameterIndex,Object x)
parameterIndex:代表的是第几个?
x 代表的是给?赋的值
使用预处理对象(PreparedStatement)实现查询操作
import java.sql.*;
import java.util.Scanner;
public class UserLogin_PS {
public static void main(String[] args) throws SQLException {
//创建Scanner对象
final Scanner scanner = new Scanner(System.in);
//调用方法键盘录入要登录的用户名和密码
System.out.println("请你输入用户名");
String username = scanner.next();
System.out.println("请你输入密码");
String password = scanner.next();
//获取连接
Connection conn = JDBCUtils.getConn();
//准备sql语句
//String sql = "select * from user where username = " + username + "and password =" + password;
String sql = "select * from user where username = ? and password = ? ";
//获取执行平台
//Statement statement = conn.createStatement();
PreparedStatement preparedStatement = conn.prepareStatement(sql);
//执行sql语句
preparedStatement.setObject(1,username);
preparedStatement.setObject(2,password);
ResultSet resultSet = preparedStatement.executeQuery();
if (resultSet.next()){
System.out.println("登录成功");
}else{
System.out.println("登录失败");
}
JDBCUtils.close(conn,preparedStatement,resultSet);
}
使用预处理对象(PreparedStatement)实现插入,删除,修改操作
public class Demo01PreparedS {
@Test
public void insert() throws SQLException {
//获取连接
Connection conn = JDBCUtils.getConn();
//准备sql
String sql = "insert into `user` values(?,?,?)";
//获取连接
PreparedStatement preparedStatement = conn.prepareStatement(sql);
preparedStatement.setObject(1,null);
preparedStatement.setObject(2,"张大彪");
preparedStatement.setObject(3,"8888");
preparedStatement.executeUpdate();
JDBCUtils.close(conn,preparedStatement,null);
}
@Test
public void delete() throws SQLException {
//获取连接
Connection conn = JDBCUtils.getConn();
//写sql
String sql = "delete from `user` where uid = ?";
PreparedStatement preparedStatement = conn.prepareStatement(sql);
preparedStatement.setObject(1,6);
preparedStatement.executeUpdate();
}
@Test
public void update() throws SQLException {
//获取连接
Connection conn = JDBCUtils.getConn();
//写sql
String sql = "update `user` set username = ? where uid = ?";
PreparedStatement preparedStatement = conn.prepareStatement(sql);
preparedStatement.setObject(1,"楚云飞");
preparedStatement.setObject(2,7);
preparedStatement.executeUpdate();
}
}
改造JDBC工具类——结合properties文件
编写第二种工具类
public class JDBCUtils_Two {
private static String url = null;
private static String username = null;
private static String password = null;
/*
* 注册驱动,数据库url,用户名,密码
* 这四大参数应该上来就先初始化
* 而且只需要初始化一次即可
* 所以我们需要放在静态代码中
* */
static{
Properties properties = new Properties();
try {
FileInputStream fileInputStream = new FileInputStream("D:\\untitled7\\JDBC_DAY04\\src\\JDBC_EXC\\properities.txt");
properties.load(fileInputStream);
Class.forName(properties.getProperty("driverClass"));
url = properties.getProperty("url");
username = properties.getProperty("username");
password = properties.getProperty("password");
} catch (IOException e) {
e.printStackTrace();
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
//获取连接
public static Connection getConn() throws SQLException, ClassNotFoundException {
Connection conn = null;
conn = DriverManager.getConnection(url,username,password);
return conn;
}
//关闭资源
public static void close(Connection conn, Statement st, ResultSet rs){
if (rs!= null){
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (st!= null){
try {
st.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (conn!= null){
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
进行测试
public class Demo02PrepareS {
public static void main(String[] args) throws SQLException, ClassNotFoundException {
//获取连接
Connection conn = JDBCUtils_Two.getConn();
//准备sql
String sql = "insert into user values(?,?,?);";
//获取prepareStatement对象
PreparedStatement preparedStatement = conn.prepareStatement(sql);
//给?赋值
preparedStatement.setObject(1,null);
preparedStatement.setObject(2,"李云龙");
preparedStatement.setObject(3,"3333");
//执行sql
preparedStatement.executeUpdate();
//关闭资源
JDBCUtils_Two.close(conn,preparedStatement,null);
}
}
PrepareStatement预处理对象
建立一个新表
CREATE TABLE category(
cid INT PRIMARY KEY AUTO_INCREMENT,
cname VARCHAR(20)
);
MySQL批量添加数据
添加properties文件
driverClass=com.mysql.cj.jdbc.Driver
username=root
password=12345678
url=jdbc:mysql://192.168.10.100:3306/bigdata?rewriteBatchedStatements=true?useUnicode=true&characterEncoding=utf8
1.在设置完所有要添加的参数,调用PreparedStatement中的addBatch(),将sql语句添加到PrepareStatement中
2.调用preparedstatement中的executeBatch()方法批量处理sql语句
public class JDBCUtils {
private static String url = null;
private static String username = null;
private static String password = null;
/*
* 注册驱动,数据库url,用户名,密码
* 这四大参数应该上来就先初始化
* 而且只需要初始化一次即可
* 所以我们需要放在静态代码中
* */
static{
Properties properties = new Properties();
try {
FileInputStream fileInputStream = new FileInputStream("D:\\untitled7\\JDBC_DAY04\\src\\JDBC_EXC\\properities.txt");
properties.load(fileInputStream);
Class.forName(properties.getProperty("driverClass"));
url = properties.getProperty("url");
username = properties.getProperty("username");
password = properties.getProperty("password");
} catch (IOException e) {
e.printStackTrace();
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
//获取连接
public static Connection getConn() throws SQLException, ClassNotFoundException {
Connection conn = null;
conn = DriverManager.getConnection(url,username,password);
return conn;
}
//关闭资源
public static void close(Connection conn, Statement st, ResultSet rs){
if (rs!= null){
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (st!= null){
try {
st.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (conn!= null){
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
public class test01 {
@Test
public void insert() throws SQLException, ClassNotFoundException {
//1.获取连接
Connection conn = JDBCUtils.getConn();
//2.准备sql
String sql = "insert into category(cname) values(?);";
//3.准备执行平台
PreparedStatement preparedStatement = conn.prepareStatement(sql);
/*
* 执行sql mysql默认情况下,会把多条要执行的sql拆开来,一个一个执行
* 如果要是批处理,可以理解为将多条要执行的sql看成是一组操作,不拆分执行
* */
for (int i = 0; i < 100; i++) {
preparedStatement.setObject(1,"箱包"+i);
/*
* void addBatch()
* 将一组参数添加到此PreparedStatement对象的批处理命令中
* */
preparedStatement.addBatch();
}
/*
* 批量执行
* executeBatch() 将一批命令提交给数据库来执行,全部命令执行成功
* */
preparedStatement.executeBatch();
//关闭资源
JDBCUtils.close(conn,preparedStatement,null);
}
}
连接池
1.为啥要学连接池
我们平时要频繁的创建连接,销毁链接,比较耗费内存资源,所以我们需要学习连接池,里面装有很多条连接对象,使用的时候从连接池中获取,使用完毕后,归还连接池
2.连接池接口:DateSource ------ 连接池的一个标准
3.常见的连接池
Druid c3P0
连接池之c3p0(拓展)
1.导入c3p的jar包
c3p0-0.9.1.2.jar
2.创建xml配置文件
a.创建file
b.取名:xxx.xml -> c3p0-config.xml ----> 文件名不能错
3.xml 文件内容:复制粘贴
<c3p0-config>
<!-- 使用默认的配置读取连接池对象 -->
<default-config>
<!-- 连接参数 -->
<property name="driverClass">com.mysql.cj.jdbc.Driver</property>
<property name="jdbcUrl">jdbc:mysql://192.168.10.100:3306/bigdata?rewriteBatchedStatements=true&useUnicode=true&characterEncoding=UTF8</property>
<property name="user">root</property>
<property name="password">root</property>
<!--
连接池参数
初始连接数(initialPoolSize):刚创建好连接池的时候准备的连接数量
最大连接数(maxPoolSize):连接池中最多可以放多少个连接
最大等待时间(checkoutTimeout):连接池中没有连接时最长等待时间
最大空闲回收时间(maxIdleTime):连接池中的空闲连接多久没有使用就会回收
-->
<property name="initialPoolSize">5</property>
<property name="maxPoolSize">10</property>
<property name="checkoutTimeout">2000</property>
<property name="maxIdleTime">1000</property>
</default-config>
</c3p0-config>
注意jdbcUrl的内容在xml中,用&加分号 用来替代&
编写c3p0工具类
1.接口:DataSource接口
2.实现类:ComboPoolDataSource()
3.创建:
dataSource = new ComboPoolDataSource()
4.获取连接:
dataSource.getConnection()
以下是代码实现
import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class C3P0Utils {
//声明一个连接池对象
private static DataSource dataSource = null;
/*
* 注册驱动,数据库url,用户名,密码
* 这四大参数应该上来就先初始化
* 而且只需要初始化一次即可
* 所以我们应该放到静态代码中
* */
static{
dataSource = new ComboPooledDataSource();
}
/*
* 获取连接
* */
public static Connection getConn(){
Connection connection = null;
try {
connection = dataSource.getConnection();
} catch (SQLException e) {
e.printStackTrace();
}
return connection;
}
/*
* 关闭资源
* 此时Connection的close不是销毁对象而是归还连接池
* */
public static void close(Connection connection, Statement statement, ResultSet resultSet){
if (connection != null){
try {
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (statement != null){
try {
statement.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (resultSet != null){
try {
resultSet.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
测试代码
public class Demo01_C3 {
@Test
public void insert() throws SQLException {
//获取连接
Connection connection = C3P0Utils.getConn();
System.out.println(connection);
//编写sql
String sql = "insert into category(cname) values(?);";
//获取preparestatement对象
PreparedStatement preparedStatement = connection.prepareStatement(sql);
//给?赋值
preparedStatement.setObject(1,"蔬菜");
//执行sql
preparedStatement.executeUpdate();
//关闭资源
C3P0Utils.close(connection,preparedStatement,null);
}
}
1.xml:可拓展性标记语言 ---- 标签名自定义 标记语言:所有的内容,都是由标签组成
2.标签
a:闭合标签:一个标签由开始标签和结束标签组成
标签体
b: 自闭合标签:
连接池之Druid(德鲁伊)
1.概述:是阿里巴巴开发的,号称目前商业界最可靠的连接池
2.导jar包:
druid-1.1.10.jar
3.准备druid的properties配置文件
a.取名:druid.properties
b.写配置:
driver=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://localhost:3306/220227_java4
username=root
password=root
initialSize=5
maxActive=10
maxWait=1000
4.怎么读取配置文件
DruidDataSourceFactory.createDataSource(properties集合)
代码示例
driver=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://192.168.10.100:3306/bigdata?useUnicode=true&characterEncoding=UTF8
username=root
password=12345678
initialSize=5
maxActive=10
maxWait=1000
Druid工具类
public class DruidUtils {
//声明一个连接池对象
private static DataSource dataSource = null;
/*
* 注册驱动,数据库url,用户名 密码
* 这四大参数应该上来就先初始化
* 而且只需要初始化一次即可
* 所以我们应该放到静态代码块中
* */
static {
Properties properties = new Properties();
InputStream in = DruidUtils.class.getClassLoader().getResourceAsStream("druid.properties");
try {
properties.load(in);
dataSource = DruidDataSourceFactory.createDataSource(properties);
} catch (IOException e) {
e.printStackTrace();
} catch (Exception e) {
e.printStackTrace();
}
}
//获取连接
public static Connection getConn(){
Connection conn = null;
try {
conn = dataSource.getConnection();
} catch (SQLException e) {
e.printStackTrace();
}
return conn;
}
/*
* 关闭资源
* 此时connection的close不是销毁对象,而是归还连接池
* */
public static void close(Connection conn, Statement statement, ResultSet resultSet){
if (conn != null){
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (statement != null){
try {
statement.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (resultSet != null){
try {
resultSet.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
测试代码
public class test03_druid {
public static void main(String[] args) throws SQLException {
//获取连接
Connection conn = DruidUtils.getConn();
//编写sql
String sql = "insert into category(cname) values(?);";
//获取PreparedStatement对象
PreparedStatement preparedStatement = conn.prepareStatement(sql);
//给?赋值
preparedStatement.setObject(1,"猪肉");
//执行sql
preparedStatement.executeUpdate();
//关闭资源
DruidUtils.close(conn,preparedStatement,null);
}
}