【JDBC入门】
今日内容
- JDBC基本概念
- 快速入门
- 对JDBC中各个接口和类详解
1 JDBC
1.1 概念Java DataBase Connectivity
Java数据库连接,Java语言操作数据库
1.2 快速入门
- 导入驱动jar包 mysql-connector-java-5.1.37-bin.jar
复制jar包到项目的libs目录下,右键->Add As Library - 注册驱动
- 获取数据库连接对象Connection
- 定义sql语句
- 获取执行sql语句的对象Statement
- 执行sql,接受返回结果
- 处理结果
- 释放资源
//1. 导入驱动jar包
//2.注册驱动
Class.forName("com.mysql.jdbc.Driver");
//3.获取数据库连接对象
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/db3", "root", "root");
//4.定义sql语句
String sql = "update account set balance = 500 where id = 1";
//5.获取执行sql的对象 Statement
Statement stmt = conn.createStatement();
//6.执行sql
int count = stmt.executeUpdate(sql);
//7.处理结果
System.out.println(count);
//8.释放资源
stmt.close();
conn.close();
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
1.3 详解各个对象
-
DriverManager:驱动管理对象
1.注册驱动:告诉程序使用哪一个数据库驱动jar包Class.forName("com.mysql.jdbc.Driver");
注意:mysql 5 之后的驱动jar包可以省略此步骤
2.获取数据库连接:
static Connection getConnection(String url,String user,String password)
参数:- url:指定连接的路径
jdbc:mysql://ip地址(域名):端口号/数据库名称
jdbc:mysql://localhost:3306/db3 - user:用户名
- password:密码
- url:指定连接的路径
-
Connection:数据库连接对象
- 获取执行sql的对象
- Statement createStatement()
- PreparedStatement prepareStatement(String sql)
- 管理实务:
- 开启事务:setAutoCommit(boolean autoCommit)
调用该方法参数设置false,即开启事务 - 提交事务:commit()
- 回滚事务:rollback()
- 开启事务:setAutoCommit(boolean autoCommit)
- 获取执行sql的对象
-
Statement:执行sql的对象
- 执行sql
- boolean execute(String sql):可以执行任意的sql(了解)
- int executeUpdate(String sql):执行DML(insert、update、delete)语句、DDL(create、alter、drop)语句
- ResultSet executeQuery(String sql):执行DQL(select)语句
Statement stmt = null; Connection conn = null; try { //1. 注册驱动 Class.forName("com.mysql.jdbc.Driver"); //2. 定义sql String sql = "insert into account values(null,'王五',3000)"; //3.获取Connection对象 conn = DriverManager.getConnection("jdbc:mysql:///db3", "root", "root"); //4.获取执行sql的对象 Statement stmt = conn.createStatement(); //5.执行sql int count = stmt.executeUpdate(sql);//影响的行数 //6.处理结果 System.out.println(count); if(count > 0){ System.out.println("添加成功!"); }else{ System.out.println("添加失败!"); }
- 执行sql
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}finally {
//stmt.close();
//7. 释放资源
//避免空指针异常
if(stmt != null){
try {
stmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(conn != null){
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
ResultSet:结果及对象,封装查询结果
- boolean next():游标向下移动一行,判断当前是否是最后一行末尾(是否有数据),如果是,返回false,否则返回true
- getXxx(参数):获取数据
Xxx:代表数据类型,如:int getInt()
参数:- int:代表列的编号,从1开始 getString(1)
- String:代表列名称 getDouble(“score”)
- 使用步骤:
- 游标向下移动一行
- 判断是否有数据
- 获取数据
//循环判断游标是否是最后一行末尾。 while(rs.next()){ //获取数据 //6.2 获取数据 int id = rs.getInt(1); String name = rs.getString("name"); double balance = rs.getDouble(3);
System<span class="token punctuation">.</span>out<span class="token punctuation">.</span><span class="token function">println</span><span class="token punctuation">(</span>id <span class="token operator">+</span> <span class="token string">"---"</span> <span class="token operator">+</span> name <span class="token operator">+</span> <span class="token string">"---"</span> <span class="token operator">+</span> balance<span class="token punctuation">)</span><span class="token punctuation">;</span>
}
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
PreparedStatement:执行sql对象
2 抽取JDBC工具类:JDBCUtils
2.1 目的:简化书写
2.2 分析:
- 注册驱动也抽取
- 抽取一个方法获取连接对象
- 抽取一个方法释放资源
public class JDBCUtils { private static String url; private static String user; private static String password; private static String driver; //文件只需要读取一次,使用静态代码块 static{ //读取资源文件,获取值 try { //1.创建properties集合类 Properties pro = new Properties(); //用ClassLoader类加载器获取src路径下文件 ClassLoader classLoader=JDBCUtils.class.getClassLoader(); URL res = classLoader.getResource("jdbc.properties"); String path = res.getPath(); sout(path);//绝对路径 //2. 加载文件 pro.load(new FileReader(path)) //3. 获取数据,赋值 url = pro.getProperty("url"); user = pro.getProperty("user"); password = pro.getProperty("password"); driver = pro.getProperty("driver"); //4. 注册驱动 Class.forName(driver); } catch (IOException e) { e.printStackTrace(); } catch (ClassNotFoundException e) { e.printStackTrace(); } } //获取连接对象方法 public static Connection getConnection() throws SQLExection { return DriverManager.getConnection(url,user,password); } //释放资源 public static void close(Statement stmt,Connection conn) { } public static void close(ResultSet rs,Statement stmt,Connection conn) { if(rs!=null) { try{ rs.close(); }catch (SQLException e) { e.printStackTrance(); } } if( stmt != null){ try { stmt.close(); } catch (SQLException e) { e.printStackTrace(); } }
<span class="token keyword">if</span><span class="token punctuation">(</span> conn <span class="token operator">!=</span> null<span class="token punctuation">)</span><span class="token punctuation">{</span> <span class="token keyword">try</span> <span class="token punctuation">{</span> conn<span class="token punctuation">.</span><span class="token function">close</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span> <span class="token punctuation">}</span> <span class="token keyword">catch</span> <span class="token punctuation">(</span><span class="token class-name">SQLException</span> e<span class="token punctuation">)</span> <span class="token punctuation">{</span> e<span class="token punctuation">.</span><span class="token function">printStackTrace</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span> <span class="token punctuation">}</span> <span class="token punctuation">}</span> <span class="token punctuation">}</span>
}
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 54
- 55
- 56
- 57
- 58
- 59
- 60
- 61
- 62
- 63
3 JDBC控制事务
3.1 事务:
一个包含多个步骤的业务操作。如果这个业务操作被事务管理,则这多个步骤要么同时成功,要么同时失败。
3.2 操作:
- 开启事务
- 提交事务
- 回滚事务
3.3 使用Connection对象来管理事务
- 开启事务:setAutoCommit(boolean autoCommit)
调用该方法参数传递false,即开启事务
*在执行sql语句之前开启事务 - 提交事务:commit()
*当所有sql都执行完成提交事务 - 回滚事务:rollback()
*在catch中回滚事务public class JDBCDemo { public static void main(String[] args) { Connection conn = null; PreparedStatement pstmt1 = null; PreparedStatement pstmt2 = null; try { //1. 获取连接 conn = JDBCUtils.getConnection(); //开启事务 conn.setAutoCommit(false); //2. 定义sql String sql1 = "update account set balance = balance - ? where id = ?"; String sql2 ="update account set balance + ? where id = ?"; //3. 获取执行sql对象 pstmt1 = conn.preparedStatement(sql1); pstmt2 = conn.preparedStatement(sql2); //4. 设置参数 pstmt1.setDouble(1,500); pstmt1.setInt(2,1); pstmt2.setDouble(1,500); pstmt2.setInt(2,2); //5. 执行sql语句 pstmt1.executeUpdate(); //制造异常 int i = 3/0; Pstmt2.executeUpdate(); conn.commit(); } catch (Exection e) { //事务回滚 try { if(conn!=null) { conn.rollback(); } } catch (SQLException e1) { e1.printStackTrace(); } e.printStackTrace(); }finally { JDBCUtils.close(pstmt1,conn); JDBCUtils.close(pstmt2,null); } } }
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
</div>
<link href="https://csdnimg.cn/release/phoenix/mdeditor/markdown_views-df60374684.css" rel="stylesheet">
</div>
今日内容
- JDBC基本概念
- 快速入门
- 对JDBC中各个接口和类详解
1 JDBC
1.1 概念Java DataBase Connectivity
Java数据库连接,Java语言操作数据库
1.2 快速入门
- 导入驱动jar包 mysql-connector-java-5.1.37-bin.jar
复制jar包到项目的libs目录下,右键->Add As Library - 注册驱动
- 获取数据库连接对象Connection
- 定义sql语句
- 获取执行sql语句的对象Statement
- 执行sql,接受返回结果
- 处理结果
- 释放资源
//1. 导入驱动jar包
//2.注册驱动
Class.forName("com.mysql.jdbc.Driver");
//3.获取数据库连接对象
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/db3", "root", "root");
//4.定义sql语句
String sql = "update account set balance = 500 where id = 1";
//5.获取执行sql的对象 Statement
Statement stmt = conn.createStatement();
//6.执行sql
int count = stmt.executeUpdate(sql);
//7.处理结果
System.out.println(count);
//8.释放资源
stmt.close();
conn.close();
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
1.3 详解各个对象
-
DriverManager:驱动管理对象
1.注册驱动:告诉程序使用哪一个数据库驱动jar包Class.forName("com.mysql.jdbc.Driver");
注意:mysql 5 之后的驱动jar包可以省略此步骤
2.获取数据库连接:
static Connection getConnection(String url,String user,String password)
参数:- url:指定连接的路径
jdbc:mysql://ip地址(域名):端口号/数据库名称
jdbc:mysql://localhost:3306/db3 - user:用户名
- password:密码
- url:指定连接的路径
-
Connection:数据库连接对象
- 获取执行sql的对象
- Statement createStatement()
- PreparedStatement prepareStatement(String sql)
- 管理实务:
- 开启事务:setAutoCommit(boolean autoCommit)
调用该方法参数设置false,即开启事务 - 提交事务:commit()
- 回滚事务:rollback()
- 开启事务:setAutoCommit(boolean autoCommit)
- 获取执行sql的对象
-
Statement:执行sql的对象
- 执行sql
- boolean execute(String sql):可以执行任意的sql(了解)
- int executeUpdate(String sql):执行DML(insert、update、delete)语句、DDL(create、alter、drop)语句
- ResultSet executeQuery(String sql):执行DQL(select)语句
Statement stmt = null; Connection conn = null; try { //1. 注册驱动 Class.forName("com.mysql.jdbc.Driver"); //2. 定义sql String sql = "insert into account values(null,'王五',3000)"; //3.获取Connection对象 conn = DriverManager.getConnection("jdbc:mysql:///db3", "root", "root"); //4.获取执行sql的对象 Statement stmt = conn.createStatement(); //5.执行sql int count = stmt.executeUpdate(sql);//影响的行数 //6.处理结果 System.out.println(count); if(count > 0){ System.out.println("添加成功!"); }else{ System.out.println("添加失败!"); }
- 执行sql