JDBC笔记

于 2020-08-26 11:16:44 发布
阅读量108 收藏
点赞数
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.youkuaiyun.com/qq_43120504/article/details/108236236
版权

JDBC

JDBC（Java数据库连接）是一种用于执行SQL语句的Java API，由一组用Java语言编写的类和接口组成。

1.第一个JDBC程序

package com.mashiro.jdbctest01;

import java.sql.*;

public class JdbcFirstDemo {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        //1.加载驱动
        Class.forName("com.mysql.jdbc.Driver");
        //2.用户信息 && url
        String url = "jdbc:mysql://localhost:3306/jdbcstudy?serverTimezone=UTC&useUnicode=true&characterEncoding=utf8";
        String name = "root";
        String password = "123456";
        //3.连接成功
        Connection connection = DriverManager.getConnection(url,name,password);
        //4.执行sql的对象
        Statement statement = connection.createStatement();
        //5.执行sql
        String sql = "SELECT * FROM users";
        ResultSet resultSet = statement.executeQuery(sql);
        while(resultSet.next()){
            System.out.println("id="+ resultSet.getObject("id"));
            System.out.println("password="+ resultSet.getObject("password"));
            System.out.println("name="+ resultSet.getObject("NAME"));
            System.out.println("email="+ resultSet.getObject("email"));
            System.out.println("bithday="+ resultSet.getObject("birthday"));
            System.out.println("========================================");
        }
        //6.关闭
        resultSet.close();
        statement.close();
        connection.close();
    }

}
  • 步骤总结:
    • 加载驱动
    • 连接数据库 DriverManager
    • 获得执行sql的对象
    • 获得返回的结果集 Statement
    • 释放连接

2.statement类

  • properties类
driver = com.mysql.jdbc.Driver
url = jdbc:mysql://localhost:3306/jdbcstudy?serverTimezone=UTC&useUnicode=true&characterEncoding=utf8
username = root
password = 123456
  • 提取工具类
package com.mashiro.jdbctest02.utils;

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

public class JdbcUtils {

    private static String driver = null;
    private static String url = null;
    private static String username = null;
    private static String password = null;

    static {
        try {
            InputStream in = JdbcUtils.class.getClassLoader().getResourceAsStream("db.properties");
            Properties properties = new Properties();
            properties.load(in);

            driver = properties.getProperty("driver");
            url = properties.getProperty("url");
            username = properties.getProperty("username");
            password = properties.getProperty("password");

            //1.驱动只需要加载一次
            Class.forName(driver);


        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    // 获取连接
    public static Connection getConnection() throws SQLException {
        return DriverManager.getConnection(url, username, password);
    }
    // 释放资源
    public static void release(Connection connection, Statement statement, ResultSet resultSet)  {
        if (resultSet != null){
            try {
                resultSet.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        if (statement != null){
            try {
                statement.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        if (connection != null){
            try {
                connection.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }

}
  • 增
package com.mashiro.jdbctest02;

import com.mashiro.jdbctest02.utils.JdbcUtils;

import java.sql.*;

public class TestInsert {
    public static void main(String[] args) {
        Connection connection = null;
        Statement statement = null;
        ResultSet resultSet = null;

        try {
            connection = JdbcUtils.getConnection(); // 获取连接
            statement = connection.createStatement();
            String sql = "INSERT INTO users(id,`NAME`,`PASSWORD`,`email`,`birthday`)" +
                        "VALUES(4,'mashiro','123456','2571693608@qq.com','2020-08-24')";
            int i = statement.executeUpdate(sql);
            if (i > 0){
                System.out.println("插入成功");
            }
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            JdbcUtils.release(connection,statement,resultSet);
        }
    }
}
  • 删
package com.mashiro.jdbctest02;

import com.mashiro.jdbctest02.utils.JdbcUtils;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;

public class TestDelete {
    public static void main(String[] args) {
        Connection connection = null;
        Statement statement = null;
        ResultSet resultSet = null;

        try {
            connection = JdbcUtils.getConnection(); // 获取连接
            statement = connection.createStatement();
            String sql = "DELETE FROM `users` WHERE `id`=4";
            int i = statement.executeUpdate(sql);
            if (i > 0){
                System.out.println("删除成功");
            }
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            JdbcUtils.release(connection,statement,resultSet);
        }
    }
}
  • 改
package com.mashiro.jdbctest02;

import com.mashiro.jdbctest02.utils.JdbcUtils;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;

public class TestUpdate {
    public static void main(String[] args) {
        Connection connection = null;
        Statement statement = null;
        ResultSet resultSet = null;

        try {
            connection = JdbcUtils.getConnection(); // 获取连接
            statement = connection.createStatement();
            String sql = "UPDATE `users` SET `name`='nagisa' WHERE `id` = 4";
            int i = statement.executeUpdate(sql);
            if (i > 0){
                System.out.println("更新成功");
            }
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            JdbcUtils.release(connection,statement,resultSet);
        }
    }
}
  • 查
package com.mashiro.jdbctest02;

import com.mashiro.jdbctest02.utils.JdbcUtils;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;

public class TestSelect {
    public static void main(String[] args) {
        Connection connection = null;
        Statement statement = null;
        ResultSet resultSet = null;

        try {
            connection = JdbcUtils.getConnection(); // 获取连接
            statement = connection.createStatement();
            String sql = "SELECT * FROM `users`";
            statement.executeQuery(sql);
            resultSet = statement.getResultSet();

            while(resultSet.next()){
                System.out.println(resultSet.getInt("id"));
                System.out.println(resultSet.getString("NAME"));
                System.out.println(resultSet.getString("PASSWORD"));
                System.out.println(resultSet.getString("email"));
                System.out.println(resultSet.getString("birthday"));
            }
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            JdbcUtils.release(connection,statement,resultSet);
        }
    }
}

2.PreparedStatement类

优点： 效率高，防止SQL注入

  • 增
package com.mashiro.jdbctest03;

import com.mashiro.jdbctest02.utils.JdbcUtils;
import java.sql.Connection;
import java.util.Date;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class TestInsert {
    public static void main(String[] args) {
        Connection connection = null;
        PreparedStatement st = null;

        try {
            connection = JdbcUtils.getConnection();
            String sql = "insert into users(`id`,`NAME`,`PASSWORD`,`email`,`birthday`) value(?,?,?,?,?)";
            st = connection.prepareStatement(sql);

            st.setInt(1,5);
            st.setString(2,"Shina");
            st.setString(3,"123456");
            st.setString(4,"112233@qq.com");
            st.setDate(5,new java.sql.Date(new Date().getTime()));

            int i = st.executeUpdate();
            if (i > 0){
                System.out.println("插入成功");
            }



        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            JdbcUtils.release(connection,st,null);
        }
    }
}
  • 删
package com.mashiro.jdbctest03;

import com.mashiro.jdbctest02.utils.JdbcUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Date;

public class TestDelete {
    public static void main(String[] args) {
        Connection connection = null;
        PreparedStatement st = null;

        try {
            connection = JdbcUtils.getConnection();
            String sql = "DELETE FROM users WHERE id = ?";
            st = connection.prepareStatement(sql);

            st.setInt(1,5);

            int i = st.executeUpdate();
            if (i > 0){
                System.out.println("删除成功");
            }

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            JdbcUtils.release(connection,st,null);
        }
    }
}
  • 改
package com.mashiro.jdbctest03;

import com.mashiro.jdbctest02.utils.JdbcUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Date;

public class TestUpdate {
    public static void main(String[] args) {
        Connection connection = null;
        PreparedStatement st = null;

        try {
            connection = JdbcUtils.getConnection();
            String sql = "Update users set NAME = ?  where id = ?";
            st = connection.prepareStatement(sql);

            st.setString(1,"syokora");
            st.setInt(2,3);


            int i = st.executeUpdate();
            if (i > 0){
                System.out.println("更新成功");
            }



        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            JdbcUtils.release(connection,st,null);
        }
    }
}
  • 查
package com.mashiro.jdbctest03;

import com.mashiro.jdbctest02.utils.JdbcUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class TestSelect {
    public static void main(String[] args) {
        Connection connection = null;
        PreparedStatement st = null;
        ResultSet resultSet = null;

        try {
            connection = JdbcUtils.getConnection();
            String sql = "select * from users where id = ?";

            st = connection.prepareStatement(sql);
            st.setInt(1,5);

            st.executeQuery();
            resultSet = st.getResultSet();
            while (resultSet.next()){
                System.out.println("id="+resultSet.getInt("id"));
                System.out.println("NAME="+resultSet.getString("NAME"));
                System.out.println("PASSWORD="+resultSet.getString("PASSWORD"));
                System.out.println("email="+resultSet.getString("email"));
                System.out.println("birthday="+resultSet.getString("birthday"));
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            JdbcUtils.release(connection,st,resultSet);
        }
    }
}

3.事务

package com.mashiro.jdbctest04;

import com.mashiro.jdbctest02.utils.JdbcUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class TestTransaction {
    public static void main(String[] args) {
        Connection connection = null;
        PreparedStatement st = null;
        ResultSet rs = null;

        try {
            connection =  JdbcUtils.getConnection();
            // 关闭自动提交并开启事务
            connection.setAutoCommit(false);

            String sql1 = "update account set money=money-100 where name='A'";
            st = connection.prepareStatement(sql1);
            st.executeUpdate();

            String sql2 = "update account set money=money+100 where name='B'";
            st = connection.prepareStatement(sql2);
            st.executeUpdate();

            // 业务执行完毕，提交事务
            connection.commit();
            System.out.println("success！");

        } catch (SQLException throwables) {
            try {
                // 失败回滚
                connection.rollback();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            throwables.printStackTrace();
        } finally {
            JdbcUtils.release(connection,st,rs);
        }
    }
}

DBCP

  • dbcpconfig.properties
#连接设置
driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8
#url = jdbc:mysql://localhost:3306/jdbcstudy?serverTimezone=UTC&useUnicode=true&characterEncoding=utf8
username=root
password=123456

#<!-- 初始化连接 -->
initialSize=10

#最大连接数量
maxActive=50

#<!-- 最大空闲连接 -->
maxIdle=20

#<!-- 最小空闲连接 -->
minIdle=5

#<!-- 超时等待时间以毫秒为单位 6000毫秒/1000等于60秒 -->
maxWait=60000
#JDBC驱动建立连接时附带的连接属性属性的格式必须为这样：【属性名=property;】
#注意："user" 与 "password" 两个属性会被明确地传递，因此这里不需要包含他们。
connectionProperties=useUnicode=true;characterEncoding=UTF8

#指定由连接池所创建的连接的自动提交（auto-commit）状态。
defaultAutoCommit=true

#driver default 指定由连接池所创建的连接的只读（read-only）状态。
#如果没有设置该值，则“setReadOnly”方法将不被调用。（某些驱动并不支持只读模式，如：Informix）
defaultReadOnly=

#driver default 指定由连接池所创建的连接的事务级别（TransactionIsolation）。
#可用值为下列之一：（详情可见javadoc。）NONE,READ_UNCOMMITTED, READ_COMMITTED, REPEATABLE_READ, SERIALIZABLE
defaultTransactionIsolation=READ_UNCOMMITTED
  • 工具类 DbcpUtils
package com.mashiro.jdbctest05.Utils;

import org.apache.commons.dbcp2.BasicDataSource;
import org.apache.commons.dbcp2.BasicDataSourceFactory;

import java.io.InputStream;
import java.sql.*;
import java.util.Properties;

public class DbcpUtils {
    private static BasicDataSource dataSource = null;
    static {
        try {
            InputStream in = DbcpUtils.class.getClassLoader().getResourceAsStream("dbcpconfig.properties");
            Properties properties = new Properties();
            properties.load(in);

            //创建数据源  工厂模式
            dataSource = BasicDataSourceFactory.createDataSource(properties);

        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    // 获取连接
    public static Connection getConnection() throws SQLException {
        // 从数据源获取连接
        return dataSource.getConnection();
    }
    // 释放资源
    public static void release(Connection connection, Statement statement, ResultSet resultSet)  {
        if (resultSet != null){
            try {
                resultSet.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        if (statement != null){
            try {
                statement.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        if (connection != null){
            try {
                connection.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }
}
  • 测试类
package com.mashiro.jdbctest05;

import com.mashiro.jdbctest05.Utils.DbcpUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Date;

public class TestDbcp {
    public static void main(String[] args) {
        Connection connection = null;
        PreparedStatement st = null;
        try {

            connection = DbcpUtils.getConnection();
            String sql = "insert into users(`id`,`NAME`,`PASSWORD`,`email`,`birthday`) value(?,?,?,?,?)";

            st = connection.prepareStatement(sql);
            st.setInt(1,6);
            st.setString(2,"panira");
            st.setString(3,"123456");
            st.setString(4,"112233@qq.com");
            st.setDate(5,new java.sql.Date(new Date().getTime()));
            int i = st.executeUpdate();
            if (i > 0){
                System.out.println("插入成功");
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DbcpUtils.release(connection,st,null);
        }
    }
}
Shina_Mashiro
关注
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值