目录
JDBC连接常规步骤
package cn.jdbc;
import java.sql.Connection;
import java.sql.Driver;
import java.sql.DriverManager;
import java.sql.Statement;
public class Demo1 {
public static void main(String[] args) throws Exception {
//注册驱动(可省略)
Class.forName("com.mysql.jdbc.Driver");
//定义sql语句
String sql = "update stu set salary = 500 where id = 2";
//获取Connection对象
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/db1", "root", "");
//获取执行sql的对象Statement
Statement stmt = conn.createStatement();
//执行sql,返回的是影响的行数
int count = stmt.executeUpdate(sql);
System.out.println(count);
stmt.close();
conn.close();
}
}
添加、修改小实例
添加数据库表中数据
package cn.jdbc;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
public class Demo2 {
public static void main(String[] args) {
Connection conn = null;
Statement stmt = null;
try {
Class.forName("com.mysql.jdbc.Driver");
String sql = "insert into stu values(null, '狗狗', 3000)";
conn = DriverManager.getConnection("jdbc:mysql:///db1", "root", "");
stmt = conn.createStatement();
int count = stmt.executeUpdate(sql);
System.out.println(count);
if (count > 0) System.out.println("添加成功");
else System.out.println("添加失败");
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
} finally {
if (stmt != null) {
try {
stmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
}
修改表中信息
package cn.jdbc;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
public class Demo3 {
public static void main(String[] args) {
Connection conn = null;
Statement stmt = null;
try {
//1.注册驱动
Class.forName("com.mysql.jdbc.Driver");
//2.获取连接对象
conn = DriverManager.getConnection("jdbc:mysql:///db1", "root", "");
//3.定义sql
String sql = "update stu set salary = 1500 where id = 4";
//4.获取执行sql对象
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("修改失败");
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
} finally {
//7.释放资源,防止空指针异常
if (stmt != null) {
try {
stmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
}
ResultSet:结果集对象,封装查询结果
不完全正确的使用
package cn.jdbc;
import java.sql.*;
public class Demo3 {
public static void main(String[] args) {
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
try {
//1.注册驱动
Class.forName("com.mysql.jdbc.Driver");
//2.获取连接对象
conn = DriverManager.getConnection("jdbc:mysql:///db1", "root", "");
//3.定义sql
String sql = "select * from stu";
//4.获取执行sql对象
stmt = conn.createStatement();
//5.执行sql
rs = stmt.executeQuery(sql);
//6.处理结果
//6.1 让游标向下移动一行,默认在表头
rs.next();
//6.2获取数据
int id = rs.getInt(1);
String name = rs.getString("name");
int salary = rs.getInt(3);
System.out.println(id + "---" + name + "---" + salary);
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
} finally {
//7.释放资源,防止空指针异常
if (rs != null) {
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (stmt != null) {
try {
stmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
}
while判断游标是否是最后一行末尾
package cn.jdbc;
import java.sql.*;
public class Demo3 {
public static void main(String[] args) {
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
try {
//1.注册驱动
Class.forName("com.mysql.jdbc.Driver");
//2.获取连接对象
conn = DriverManager.getConnection("jdbc:mysql:///db1", "root", "");
//3.定义sql
String sql = "select * from stu";
//4.获取执行sql对象
stmt = conn.createStatement();
//5.执行sql
rs = stmt.executeQuery(sql);
//6.处理结果
while (rs.next()) {
int id = rs.getInt(1);
String name = rs.getString("name");
int salary = rs.getInt(3);
System.out.println(id + "---" + name + "---" + salary);
}
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
} finally {
//7.释放资源,防止空指针异常
if (rs != null) {
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (stmt != null) {
try {
stmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
}
抽取JDBC工具类
1、注册驱动
2、抽取一个方法获取连接对象
3、抽取一个方法释放资源
配置文件:jdbc.properties
url = jdbc:mysql:///db1
user = root
password =
driver = com.mysql.jdbc.Driver
使用工具类变化后的Demo代码
package cn.jdbc;
import cn.jdbc.util.JDBCUtils;
import java.sql.*;
public class Demo3 {
public static void main(String[] args) {
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
try {
// //1.注册驱动
// Class.forName("com.mysql.jdbc.Driver");
// //2.获取连接对象
// conn = DriverManager.getConnection("jdbc:mysql:///db1", "root", "");
conn = JDBCUtils.getConnection();
//3.定义sql
String sql = "select * from stu";
//4.获取执行sql对象
stmt = conn.createStatement();
//5.执行sql
rs = stmt.executeQuery(sql);
//6.处理结果
while (rs.next()) {
int id = rs.getInt(1);
String name = rs.getString("name");
int salary = rs.getInt(3);
System.out.println(id + "---" + name + "---" + salary);
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
// //7.释放资源,防止空指针异常
// if (rs != null) {
// try {
// rs.close();
// } catch (SQLException e) {
// e.printStackTrace();
// }
// }
// if (stmt != null) {
// try {
// stmt.close();
// } catch (SQLException e) {
// e.printStackTrace();
// }
// }
// if (conn != null) {
// try {
// conn.close();
// } catch (SQLException e) {
// e.printStackTrace();
// }
// }
JDBCUtils.close(rs, stmt, conn);
}
}
}
工具类代码
package cn.jdbc.util;
import java.io.FileReader;
import java.io.IOException;
import java.net.URL;
import java.sql.*;
import java.util.Properties;
/**
* JDBC工具类
*/
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();
//获取src路径下的文件的方式---->ClassLoader 类加载器
ClassLoader classLoader = JDBCUtils.class.getClassLoader();
URL res = classLoader.getResource("jdbc.properties");
String path = res.getPath();
System.out.println(path);
//2.加载文件
pro.load(new FileReader(path));
//3.获取数据,赋值。
url = pro.getProperty("url");
user = pro.getProperty("user");
password = pro.getProperty("password");
driver = pro.getProperty("driver");
Class.forName(driver);
} catch (IOException e) {
e.printStackTrace();
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
/**
* 获取链接
* 解决:配置文件实现传参数
* @return 连接对象
*/
public static Connection getConnection() throws SQLException {
return DriverManager.getConnection(url, user, password);
}
/**
* 释放资源
* 2种
* @param stmt
* @param conn
*/
public static void close(Statement stmt, Connection conn){
if (stmt != null) {
try {
stmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
public static void close(ResultSet rs, Statement stmt, Connection conn){
if (rs != null) {
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (stmt != null) {
try {
stmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
Druid:数据库连接池
JDBCTemplate