学习大数据之JDBC(使用JAVA语句进行SQL操作)(2)

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&amp;useUnicode=true&amp;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中,用&amp加分号 用来替代&
编写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);
    }
}
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值