JDBC的概念
JDBC Java Database connectivity
Java数据库连接规范(一套接口) Sun公司提供的
是一种用于执行SQL语句的Java API,可以为多种关系数据库提供统一访问,
它由一组用Java语言编写的类和接口组成。
JDBC提供了一种基准,据此可以构建更高级的工具和接口,
使数据库开发人员能够编写数据库应用程序
JDBC四个核心类
DriverManager 创建连接
Connection 连接类
Statement 执行sql语句
ResultSet 结果集
JDBC连接步骤
1.注册驱动
2.获取连接 Connection
3.获取 sql 语句的执行对象 Statement
4.执行sql语句 返回结果集 ReaultSet
5.处理结果集
6.关闭资源
连接数据库基础操作
Class.foName("com.mysql.jdbc.Driver");
String url = "jdbc:mysql://localhost:3306/myjdbc";
Connection connection = DriverManager.getConnection(url,"root","123456");
Properties info = new Properties();
info.setProperty("user", "root");
info.setProperty("password","123456");
Connection connection = DriverManager.getConnection(url, info);
String url2 = "jdbc:mysql://localhost:3306/myjdbc?user=root&password=123456";
Connection connection = DriverManager.getConnection(url2);
Statement statement = connection.creatStatement();
String sql = "select * from users";
ResultSet resultSet = statement.executeQuery(sql);
while(result.next()){
System.out.println(resultSet.getObject(1));
System.out.println(resultSet.getObject(2));
}
resultSet.close();
statement.close();
connection.close();
连接数据的异常处理
public static void main(String[] args) {
Connection connection = null;
Statement statement = null;
ResultSet resultSet = null;
try {
Class.forName("com.mysql.jdbc.Driver");
String url = "jdbc:mysql://localhost:3306/myjdbc";
connection = DriverManager.getConnection(url,"root","123456");
statement = connection.createStatement();
String sql = "select * from users";
resultSet = statement.executeQuery(sql);
ArrayList<User> list = new ArrayList<>();
while (resultSet.next()) {
User user = new User();
user.setId(resultSet.getInt("id"));
user.setName(resultSet.getString("name"));
user.setEmail(resultSet.getString("email"));
user.setPassword(resultSet.getString("password"));
user.setBirthday(resultSet.getDate("birthday"));
list.add(user);
}
for (User user : list) {
System.out.println(user);
}
} catch (ClassNotFoundException e) {
throw new RuntimeException("驱动加载失败");
} catch (SQLException e) {
throw new RuntimeException("数据库连接失败");
}finally {
if (resultSet != null) {
try {
resultSet.close();
} catch (SQLException e) {
throw new RuntimeException("关闭失败");
}
resultSet = null;
}
if (statement != null) {
try {
statement.close();
} catch (SQLException e) {
throw new RuntimeException("关闭失败");
}
statement = null;
}if (connection != null) {
try {
connection.close();
} catch (SQLException e) {
throw new RuntimeException("关闭失败");
}
connection = null;
}
}
}
对数据库的增删改查
// 注解 用来测试方法
// 注意: 要使用public修饰 无返回值方法
@Test
public void testInsert() throws Exception {
Class.forName("com.mysql.jdbc.Driver")
String url = "jdbc:mysql://localhost:3306/myjdbc"
Connection connection = DriverManager.getConnection(url, "root", "123456")
Statement statement = connection.createStatement()
// executeUpdate 增删改使用
String sql = "insert into users values (5,'rs', '567','qq@567','1994-09-22')"
// 受影响的行数
int row = statement.executeUpdate(sql)
System.out.println(row)
if (row > 0) {
System.out.println("插入成功")
}
statement.close()
connection.close()
}
// 更新
@Test
public void test1() throws Exception {
Class.forName("com.mysql.jdbc.Driver")
String url = "jdbc:mysql://localhost:3306/myjdbc"
Connection connection = DriverManager.getConnection(url, "root", "123456")
Statement statement = connection.createStatement()
// executeUpdate 增删改使用
String sql = "update users set name='haha' where id=5"
// 受影响的行数
int row = statement.executeUpdate(sql)
System.out.println(row)
if (row > 0) {
System.out.println("更新成功" + row + "行")
}
statement.close()
connection.close()
}
// 删除
@Test
public void test2() throws Exception {
Class.forName("com.mysql.jdbc.Driver")
String url = "jdbc:mysql://localhost:3306/myjdbc"
Connection connection = DriverManager.getConnection(url, "root", "123456")
Statement statement = connection.createStatement()
// executeUpdate 增删改使用
String sql = "delete from users where id=5;"
// 受影响的行数
int row = statement.executeUpdate(sql)
System.out.println(row)
if (row > 0) {
System.out.println("删除成功")
}
statement.close()
connection.close()
}
// 查询方法
@Test
public void testSelect() throws Exception {
Class.forName("com.mysql.jdbc.Driver")
String url = "jdbc:mysql://localhost:3306/myjdbc"
Connection connection = DriverManager.getConnection(url, "root", "123456")
Statement statement = connection.createStatement()
// 查询
String sql = "select id,name,email from users"
ResultSet resultSet = statement.executeQuery(sql)
// 处理结果集
while (resultSet.next()) {
// 可以直接填字段名称
System.out.println(resultSet.getObject("id"))
System.out.println(resultSet.getObject("name"))
System.out.println(resultSet.getObject("email"))
}
resultSet.close()
statement.close()
connection.close()
}
封装方法连接数据库
public class JDBCUtil {
private static String driverClass;
private static String url;
private static String user;
private static String password;
static {
ResourceBundle rb = ResourceBundle.getBundle("dbinfo");
driverClass = rb.getString("driverClass");
url = rb.getString("url");
user = rb.getString("user");
password = rb.getString("password");
try {
Class.forName(driverClass);
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
public static Connection getConnection() throws SQLException, ClassNotFoundException {
return DriverManager.getConnection(url,user,password);
}
public static void closeAll(ResultSet resultSet, Statement statement,Connection connection) {
if (resultSet != null) {
try {
resultSet.close();
} catch (SQLException e) {
throw new RuntimeException("关闭失败");
}
resultSet = null;
}
if (statement != null) {
try {
statement.close();
} catch (SQLException e) {
throw new RuntimeException("关闭失败");
}
statement = null;
}if (connection != null) {
try {
connection.close();
} catch (SQLException e) {
throw new RuntimeException("关闭失败");
}
connection = null;
}
}
}