jdbc的使用

于 2022-09-23 13:43:15 发布
阅读量455 收藏
点赞数
分类专栏： jdbc 文章标签： java
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/m0_56195330/article/details/126937208
版权

1、连接数据库的五种方式

Driver driver = new Driver();
        String url="jdbc:mysql://localhost:3306/a_db01";
        Properties properties = new Properties();
        properties.setProperty("user","root");
        properties.setProperty("password","123456");
        Connection connect = driver.connect(url, properties);
        Statement statement = connect.createStatement();
        String sql="insert into account values(null,'张三',1000)";
        int i = statement.executeUpdate(sql);
        statement.close();
        connect.close();

Class<?> aClass = Class.forName("com.mysql.jdbc.Driver");
        Driver driver = (Driver)aClass.newInstance();
        String url="jdbc:mysql://localhost:3306/a_db01";
        Properties properties = new Properties();
        properties.setProperty("user","root");
        properties.setProperty("password","123456");
        Connection connect = driver.connect(url, properties);
        Statement statement = connect.createStatement();
        String sql="update account set balance=2000 where id=1";
        int i = statement.executeUpdate(sql);
        statement.close();
        connect.close();
        //1、5.1.6驱动加载可以不写,因为会jvm是根据jar包里面的文件名去加载
        //2、驱动加载里面的静态代码块中会完成 驱动注册
Class<?> aClass = Class.forName("com.mysql.jdbc.Driver");
        Driver driver = (Driver)aClass.newInstance();
        DriverManager.registerDriver(driver);
        Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/a_db01", "root", "123456");
        Statement statement = connection.createStatement();
        String sql="delete from account where id=1";
        statement.executeUpdate(sql);
        statement.close();
        connection.close();
Class<?> aClass = Class.forName("com.mysql.jdbc.Driver");

        Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/a_db01","root","123456");
        Statement statement = connection.createStatement();
        String sql="insert into account values(null,'李四',3000)";
        statement.executeUpdate(sql);
        statement.close();

        connection.close();

 

Properties properties = new Properties();
        properties.load(new FileInputStream("src\\my.properties"));
        String driver = properties.getProperty("driver");
        String url = properties.getProperty("url");
        String user = properties.getProperty("user");
        String password = properties.getProperty("password");

        Connection connection = DriverManager.getConnection(url, user, password);
        Statement statement = connection.createStatement();
        String sql="insert into account values(null,'小王',1000)";
        int i = statement.executeUpdate(sql);
        statement.close();
        connection.close();

2、查询和增删改 

查询使用ResultSet resultSet = statement.executeQuery(sql);

增删改用int i = statement.executeUpdate(sql);

ResultSet底层是一个ArrayList里面存放的是数组

Properties properties = new Properties();
        properties.load(new FileInputStream("src\\my.properties"));
        String url = properties.getProperty("url");
        String user = properties.getProperty("user");
        String password = properties.getProperty("password");
        String driver = properties.getProperty("driver");
        Class<?> aClass = Class.forName(driver);
        Connection connection = DriverManager.getConnection(url, user, password);
        Statement statement = connection.createStatement();
        String sql="select * from news";
        ResultSet resultSet = statement.executeQuery(sql);
        while (resultSet.next()){
            int id = resultSet.getInt(1);
            String name = resultSet.getString(2);
            System.out.println(id+"\t"+name);
        }
        resultSet.close();
        statement.close();

        connection.close();

Properties properties = new Properties();
        properties.load(new FileInputStream("src\\my.properties"));
        String driver = properties.getProperty("driver");
        String url = properties.getProperty("url");
        String user = properties.getProperty("user");
        String password = properties.getProperty("password");

        Connection connection = DriverManager.getConnection(url, user, password);
        Statement statement = connection.createStatement();
        String sql="insert into account values(null,'小王',1000)";
        int i = statement.executeUpdate(sql);
        statement.close();
        connection.close();

 3、sql注入问题

preparedStatement

1、可以使用？占位符 然后通过set方法赋值

2、可以防止sql注入

3、减少编译次数提高效率

CREATE TABLE admin(
	username VARCHAR(32),
	PASSWORD VARCHAR(32)
)
INSERT INTO admin VALUES('admin','1234567');

SELECT * FROM admin WHERE username='root' AND PASSWORD='123456';

SELECT * FROM admin WHERE username='1'OR' and password ='OR 1='1';

statement存在sql注入 

public static void main(String[] args) throws IOException, ClassNotFoundException, SQLException {
        Scanner scanner = new Scanner(System.in);
        System.out.print("请输入用户名:");
        String username=scanner.nextLine();
        System.out.print("请输入密码:");
        String pass=scanner.nextLine();
        Properties properties = new Properties();
        properties.load(new FileInputStream("src\\my.properties"));
        String url = properties.getProperty("url");
        String user = properties.getProperty("user");
        String password = properties.getProperty("password");
        String driver = properties.getProperty("driver");
        Class<?> aClass = Class.forName(driver);
        Connection connection = DriverManager.getConnection(url, user, password);
        Statement statement = connection.createStatement();
        String sql="select * from admin where username='"+username+"' and password='"+pass+"'";
        ResultSet resultSet = statement.executeQuery(sql);
        if (resultSet.next()){
            System.out.println("登录成功");
        }else {
            System.out.println("登录失败");

        }
        resultSet.close();
        statement.close();
        connection.close();

    }

preparedStatement可以防止sql注入

public static void main(String[] args) throws Exception {
        Scanner scanner = new Scanner(System.in);
        System.out.print("请输入用户名:");
        String username=scanner.nextLine();
        System.out.print("请输入密码:");
        String pass=scanner.nextLine();
        Properties properties = new Properties();
        properties.load(new FileInputStream("src\\my.properties"));
        String url = properties.getProperty("url");
        String user = properties.getProperty("user");
        String password = properties.getProperty("password");
        String driver = properties.getProperty("driver");
        Class<?> aClass = Class.forName(driver);
        Connection connection = DriverManager.getConnection(url, user, password);
        String sql="select * from admin where username=? and password = ?";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setString(1,username);
        preparedStatement.setString(2,pass);
        ResultSet resultSet = preparedStatement.executeQuery();
        if (resultSet.next()){
            System.out.println("登录成功");
        }else {
            System.out.println("登录失败");

        }
        resultSet.close();
        preparedStatement.close();
        connection.close();
    }

4、JDBCUtils 

import java.io.FileInputStream;
import java.io.IOException;
import java.sql.*;
import java.util.Properties;

/**
 * @author 西瓜君
 * @description: TODO
 * @date 2022/9/19
 */
public class JDBCUtils {
    private static String driver;
    private static String user;
    private static String password;
    private static String url;
    static {

        try {
            Properties properties = new Properties();
            properties.load(new FileInputStream("src\\my.properties"));
            driver = properties.getProperty("driver");
             user = properties.getProperty("user");
             password = properties.getProperty("password");
             url = properties.getProperty("url");
        } catch (IOException e) {
            throw new RuntimeException(e);
        }
    }

    public static Connection getConnection(){
        try {
            return (Connection) DriverManager.getConnection(url,user,password);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    public static void close(ResultSet resultSet, Statement statement,Connection connection){
        try {
            if (resultSet!=null){
                resultSet.close();
            }
            if (statement!=null){
                statement.close();
            }
            if (connection!=null){
                connection.close();
            }
        }catch (Exception e){
            throw new RuntimeException(e);
        }
    }
}

5、事物 

public static void main(String[] args) {
        Connection connection = JDBCUtils.getConnection();
        PreparedStatement preparedStatement1 =null;
        String sql1="update account set money=money-1000 where name=?";
        String sql2="update account set money=money+1000 where name=?";
        try {
            connection.setAutoCommit(false);
             preparedStatement1 = connection.prepareStatement(sql1);
             preparedStatement1.setString(1,"张三");
             preparedStatement1.executeUpdate();
//             int i=1/0;
             preparedStatement1=connection.prepareStatement(sql2);
             preparedStatement1.setString(1,"李四");
             preparedStatement1.executeUpdate();
             connection.commit();
        } catch (SQLException throwables) {
            System.out.println("执行失败");
            try {
                connection.rollback();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            throwables.printStackTrace();
        }finally {
            JDBCUtils.close(null,preparedStatement1,connection);
        }
    }

 

6、批处理 需要在url后面加上 rewriteBatchedStatements=true

批处理可以提高效率

 

 

@Test
    public void test01() throws SQLException {
        Connection connection = JDBCUtils.getConnection();
        String sql="insert into admin values(null,?,?)";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        long start = System.currentTimeMillis();
        for (int i = 0; i < 5000; i++) {
            preparedStatement.setString(1,"jack"+i);
            preparedStatement.setString(2,"666");
            preparedStatement.executeUpdate();
        }
        long end = System.currentTimeMillis();
        System.out.println(end-start);


        JDBCUtils.close(null,preparedStatement,connection);
        //3872

    }
@Test
    public void test02() throws SQLException {
        Connection connection = JDBCUtils.getConnection();
        String sql="insert into admin values(null,?,?)";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        long start = System.currentTimeMillis();
        for (int i = 0; i < 5000; i++) {
            preparedStatement.setString(1,"tom"+i);
            preparedStatement.setString(2,"666");
            preparedStatement.addBatch();
            if ((i+1)%1000==0){
                preparedStatement.executeBatch();
                preparedStatement.clearBatch();
            }
        }
        long end = System.currentTimeMillis();
        System.out.println(end-start);
        JDBCUtils.close(null,preparedStatement,connection);
        //4025
    }

7、数据库连接池 

传统方式 

1、不能控制连接的数量、可能会造成mysql的崩溃

2、连接过多会造成很大的网络开销

3、判断用户名和密码也会造成很大的开销

long start = System.currentTimeMillis();
        for (int i = 0; i < 5000; i++) {
            Connection connection = JDBCUtils.getConnection();
            JDBCUtils.close(null,null,connection);
        }
        long end = System.currentTimeMillis();
        System.out.println(end-start);

c3p0

public static void main(String[] args) throws Exception {
        Properties properties = new Properties();
        properties.load(new FileInputStream("src\\my.properties"));
        String driver = properties.getProperty("driver");
        String user = properties.getProperty("user");
        String password = properties.getProperty("password");
        String url = properties.getProperty("url");
        ComboPooledDataSource comboPooledDataSource = new ComboPooledDataSource();
        comboPooledDataSource.setDriverClass(driver);
        comboPooledDataSource.setUser(user);
        comboPooledDataSource.setPassword(password);
        comboPooledDataSource.setJdbcUrl(url);
        comboPooledDataSource.setInitialPoolSize(10);
        comboPooledDataSource.setMaxPoolSize(50);
        long start = System.currentTimeMillis();
        for (int i = 0; i < 5000; i++) {
            Connection connection = comboPooledDataSource.getConnection();
            connection.close();
        }
        long end = System.currentTimeMillis();
        System.out.println(end-start);
    }

c3p0第二种使用

<c3p0-config>

  <named-config name="hello"> 
<!-- 驱动类 -->
  <property name="driverClass">com.mysql.jdbc.Driver</property>
  <!-- url-->
  	<property name="jdbcUrl">jdbc:mysql://127.0.0.1:3306/a_db01</property>
  <!-- 用户名 -->
  		<property name="user">root</property>
  		<!-- 密码 -->
  	<property name="password">123456</property>
  	<!-- 每次增长的连接数-->
    <property name="acquireIncrement">5</property>
    <!-- 初始的连接数 -->
    <property name="initialPoolSize">10</property>
    <!-- 最小连接数 -->
    <property name="minPoolSize">5</property>
   <!-- 最大连接数 -->
    <property name="maxPoolSize">10</property>

	<!-- 可连接的最多的命令对象数 -->
    <property name="maxStatements">5</property> 
    
    <!-- 每个连接对象可连接的最多的命令对象数 -->
    <property name="maxStatementsPerConnection">2</property>
  </named-config>
</c3p0-config>
ComboPooledDataSource comboPooledDataSource = new ComboPooledDataSource("hello");
        long start = System.currentTimeMillis();
        for (int i = 0; i < 500000; i++) {
            Connection connection = comboPooledDataSource.getConnection();
            connection.close();
        }
        long end = System.currentTimeMillis();
        System.out.println(end-start);

druid的使用

#key=value
driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/a_db01?rewriteBatchedStatements=true
#url=jdbc:mysql://localhost:3306/girls
username=root
password=123456
#initial connection Size
initialSize=10
#min idle connecton size
minIdle=5
#max active connection size
maxActive=20
#max wait time (5000 mil seconds)
maxWait=5000

 

Properties properties = new Properties();
        properties.load(new FileInputStream("src\\druid.properties"));
        DataSource dataSource = DruidDataSourceFactory.createDataSource(properties);

        long start = System.currentTimeMillis();
        for (int i = 0; i < 500000; i++) {
            Connection connection = dataSource.getConnection();
            connection.close();
        }
        long end = System.currentTimeMillis();
        System.out.println(end-start);

8、JDBCUtilsByDruid 

 

public class JDBCUtilsByDruid {
    private static DataSource dataSource;
    static {
        Properties properties = new Properties();
        try {
            properties.load(new FileInputStream("src//druid.properties"));
             dataSource = DruidDataSourceFactory.createDataSource(properties);

        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }

    public static Connection getConnection(){
        try {
            return dataSource.getConnection();
        } catch (SQLException throwables) {
            throw new RuntimeException(throwables);
        }
    }

    public static void close(ResultSet resultSet, Statement statement,Connection connection){
        try {
            if (resultSet!=null){
                resultSet.close();
            }
            if (statement!=null){
                statement.close();
            }
            if (connection!=null){
                connection.close();
            }
        }catch (Exception e){
            throw new RuntimeException(e);
        }
    }
}

9、ResultSet

1、在关闭连接后resultSet就不能使用、不方便管理数据

2、resultSet取数据不够直观

3、 与数据库对应的class类叫做javaBean

传统写法 

public static void main(String[] args) throws SQLException {
        Connection connection = JDBCUtilsByDruid.getConnection();
        String sql="select * from account";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        ResultSet resultSet = preparedStatement.executeQuery();
        ArrayList<Account> accountArrayList = new ArrayList<>();
        while (resultSet.next()){

            int id = resultSet.getInt(1);
            String name = resultSet.getString(2);
            Double money = resultSet.getDouble(3);
            Account account = new Account(id, name, money);
            accountArrayList.add(account);
        }
        System.out.println(accountArrayList);
        JDBCUtilsByDruid.close(resultSet,preparedStatement,connection);

    }

改变后

@Test
    public void test03() throws SQLException {
        Connection connection = JDBCUtilsByDruid.getConnection();
        QueryRunner queryRunner = new QueryRunner();
        String sql="select * from account";
        List<Account> query = queryRunner.query(connection, sql, new BeanListHandler<>(Account.class));
        System.out.println(query);
        JDBCUtilsByDruid.close(null,null,connection);
    }

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

西瓜君的代码
关注
专栏目录
JDBC使用
qq_44804387的博客
03-20 188
Java的JDBC（与数据库的交互) 一、什么是JDBC jdbc（java database connectivity）是一个独立于特定数据库管理系统、通用的sql数据库存取和操作的公共接口。jdbc为访问不同的数据库提供了一种统一的途径，为开发者屏蔽了一些细节问题。目标是让java程序员使用jdbc可以连接任何提供了jdbc驱动程序的数据库系统。 二、JDBC的体系结构 [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-WEhzBYUh-1596855679048)(./im
JDBC使用及源码编译
weixin_53596073的博客
03-21 1981
1. JDBC简介 JDBC是Java DataBase Connectivity的缩写，它是Java程序访问数据库的标准接口。 JDBC接口是Java标准库自带的，具体的JDBC驱动是由数据库厂商提供的，JDBC驱动也是由Java语言编写的，为一个jar包，真正实现JDBC接口中的类。 openGauss数据库源自postgres，openGauss JDBC以PostgreSQL JDBC Driver 42.2.5为基准，适配openGauss数据库，增加新特性。
jdbc使用
不生产代码_代码的搬运工的博客
09-19 170
private RowMapper<T> RowMapper = BeanPropertyRowMapper.newInstance(T.class); SQL中使用绑定绑定变量的名称(参数名称绑定) T t = namedParameterJdbcTemplate.queryForObject(sql,mapParam,RowMapper); ?号绑定 List<...
JDBC的使用
csweettea的博客
12-29 1316
这里写自定义目录标题欢迎使用Markdown编辑器新的改变功能快捷键合理的创建标题，有助于目录的生成如何改变文本的样式插入链接与图片如何插入一段漂亮的代码片生成一个适合你的列表创建一个表格设定内容居中、居左、居右SmartyPants创建一个自定义列表如何创建一个注脚注释也是必不可少的KaTeX数学公式新的甘特图功能，丰富你的文章UML 图表FLowchart流程图导出与导入导出导入 欢迎使用Markdown编辑器 你好！ 这是你第一次使用 Markdown编辑器 所展示的欢迎页。如果你想学习如何使用Mar
JAVAEE中有关JDBC使用的说明书
01-03
JAVAEE中有关JDBC使用的说明书
java连接数据库jdbc使用
03-07
了解数据库联接、了解JDBC 掌握JDBC在项目开发中的使用方法 使用JDBC连接数据库 使用JDBC操作数据库 实现单表的增加删除修改
配置数据源和JDBC使用
依然爱笑的薰衣草
08-10 5447
配置数据源和JDBC使用 一. 配置数据源 无论选择Spring的哪种数据访问方式，你都需要配置一个数据源的引用。Spring提供了在Spring上下文中配置数据源bean的多种方式，包括： 通过JDBC驱动程序定义的数据源 通过JNDI查找的数据源 连接池的数据源 1.1 使用JNDI数据源 使用Java配置的话，我们可以借助JndiObjectFactoryBean中查找...
JDBC使用方法
01-22
JDBC是 Java DataBase Connective 的缩写 JDBC的使用方法 获得connection对象
JDBC 使用
virtualjxc的专栏
11-14 1350
JDBC 　　Java Database Connectivity (JDBC)是一个标准的Java API，它由一组类和接口组成，Java应用程序开发人员使用它来访问数据库和执行SQL语句。WebLogic JDBC是JDBC规范的企业级实现，它为标准的JDBC API提供了大量的扩展 JDBC基础知识 一、采用JDBC访问数据库的基本步骤： A.载入JDBC驱动程序 B.定义连接URL C.建
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值