目录
一、JDBC快速入门二、JDBC-API1、DriverManager(1)、注册驱动(2)、获取连接 2、Connection(1)、获取执行sql的对象(2)、事务管理 3、Statement:执行SQL语句(1)、执行DML和DDL语句(2)、执行DQL语句 4、ResultSet5、PreparedStatement 三、数据库连接池1、数据库连接池简介2、数据库连接池实现3、Druid数据库连接池(1)、使用步骤A、在src目录下配置druid.properties文件,配置内容如下B、Java代码 四、添加数据代码(删除和修改与之类似)一、JDBC快速入门
package com.feng.jdbc;import java.sql.Connection;import java.sql.DriverManager;import java.sql.Statement;public class JdbcDemo {public static void main(String[] args) throws Exception {//1.注册驱动(jdk1.8之后此步骤可以跳过)Class.forName("com.mysql.jdbc.Driver");//2.获取数据库连接String url = "jdbc:mysql://127.0.0.1:3306/learn_jdbc";String user = "root";String password = "******";Connection conn = DriverManager.getConnection(url, user, password);//3.定义Sql语句String sql = "UPDATE user set age=27 WHERE id=1;";//4.获取执行SQL的对象statementStatement stmt = conn.createStatement();//5.执行sqlint count = stmt.executeUpdate(sql);//6.处理结果System.out.println(count);//7.释放资源stmt.close();conn.close();}}
二、JDBC-API
1、DriverManager
(1)、注册驱动
(2)、获取连接
2、Connection
(1)、获取执行sql的对象
(2)、事务管理
package com.feng.jdbc;import java.sql.Connection;import java.sql.DriverManager;import java.sql.SQLException;import java.sql.Statement;public class JdbcDemo {public static void main(String[] args) throws Exception {//1.获取数据库连接String url = "jdbc:mysql://127.0.0.1:3306/learn_jdbc";String user = "root";String password = "*******";Connection conn = DriverManager.getConnection(url, user, password);//2.定义Sql语句String sql1 = "UPDATE user set age=27 WHERE id=1;";String sql2 = "UPDATE user set age=27 WHERE id=2;";//3.获取执行SQL的对象statementStatement stmt = conn.createStatement();try {//开启事务conn.setAutoCommit(false);//4.执行sqlint count1 = stmt.executeUpdate(sql1);System.out.println(count1);//设置异常int i = 3/0;int count2 = stmt.executeUpdate(sql2);//5.处理结果System.out.println(count2);//提交事务mit();} catch (Exception e) {//回滚事务conn.rollback();e.printStackTrace();}//7.释放资源stmt.close();conn.close();}}//使用事务管理出现异常时会回滚到之前的状态。
3、Statement:执行SQL语句
(1)、执行DML和DDL语句
package com.feng.jdbc;import java.sql.Connection;import java.sql.DriverManager;import java.sql.Statement;public class JdbcDemo2 {public static void main(String[] args) throws Exception {//1.获取数据库连接String url = "jdbc:mysql://127.0.0.1:3306/learn_jdbc";String user = "root";String password = "100228";Connection conn = DriverManager.getConnection(url, user, password);//2.定义Sql语句:以DML语句为例String sql = "INSERT INTO `user`(name,age) value('赵六','52');";//3.获取执行SQL的对象statementStatement stmt = conn.createStatement();//4.执行sql语句int count = stmt.executeUpdate(sql);//根据执行结果判断是否操作成功if (count>=1){System.out.println("添加成功");}else {System.out.println("添加失败");}//7.释放资源stmt.close();conn.close();}}
(2)、执行DQL语句
4、ResultSet
代码示例
package com.feng.jdbc;import javax.sound.midi.Soundbank;import java.sql.*;public class JdbcDemo2 {public static void main(String[] args) throws Exception {//1.获取数据库连接String url = "jdbc:mysql://127.0.0.1:3306/learn_jdbc";String user = "root";String password = "100228";Connection conn = DriverManager.getConnection(url, user, password);//2.定义Sql语句String sql = "SELECT * from user;";//3.获取执行SQL的对象statementStatement stmt = conn.createStatement();//4.执行sql语句,获的res对象ResultSet res = stmt.executeQuery(sql);//5.处理结果,遍历res中的所有数据//5.1光标下移一行,并判断当前行是否有效while (res.next()){//5.2获取数据int id = res.getInt(1);String name = res.getString(2);int age = res.getInt(3);System.out.println(id);System.out.println(name);System.out.println(age);System.out.println("***************");}//6.释放资源res.close();stmt.close();conn.close();}}
结果
5、PreparedStatement
三、数据库连接池
1、数据库连接池简介
2、数据库连接池实现
3、Druid数据库连接池
(1)、使用步骤
A、在src目录下配置druid.properties文件,配置内容如下
# 数据库驱动driverClassName=com.mysql.cj.jdbc.Driver# 数据库连接url=jdbc:mysql:///learn_jdbc?useSSL=false&useServerPrepStmts=true# 数据库用户名username=root# 数据库密码password=*******#初始化连接数量initialSize=5#最大连接数maxActive=10#获取连接最大等待时间maxWait=3000
B、Java代码
package DruidDemo;import com.alibaba.druid.pool.DruidDataSourceFactory;import javax.sql.DataSource;import java.io.FileInputStream;import java.sql.Connection;import java.util.Properties;public class Demo1 {public static void main(String[] args) throws Exception {//1.导入jar包// 2.定义配置文件// 3.加载配置文件Properties prop = new Properties();prop.load(new FileInputStream("jdbc-demo\\src\\druid.properties"));//4.获取连接池对象DataSource dataSource = DruidDataSourceFactory.createDataSource(prop);//5.获取数据库连接ConnectionConnection conn = dataSource.getConnection();System.out.println(conn);}}
结果如下
四、添加数据代码(删除和修改与之类似)
//加载配置文件Properties pro = new Properties();pro.load(new FileInputStream("C:\\Users\\fw\\Desktop\\jdbc\\jdbc-demo\\src\\druid.properties"));//创建连接池对象DataSource dataSource = DruidDataSourceFactory.createDataSource(pro);//获取连接Connection conn = dataSource.getConnection();//定义sql语句String sql = "INSERT INTO tb_brand(brand_name,company_name,ordered,description,status) VALUES(?,?,?,?,?);";//获取pstmt对象PreparedStatement pstmt = conn.prepareStatement(sql);//传入参数String brandName = "oppo";String companyName = "oppo有限公司";int order = 20;String description = "充电5分钟,通话两小时";int status = 1;pstmt.setString(1, brandName);pstmt.setString(2, companyName);pstmt.setInt(3, order);pstmt.setString(4, description);pstmt.setInt(5, status);//执行sqlint count = pstmt.executeUpdate();System.out.println(count>0);//关闭资源pstmt.close();conn.close();