JDBC的连接

最新推荐文章于 2024-10-07 23:02:18 发布

原创最新推荐文章于 2024-10-07 23:02:18 发布 · 235 阅读

0 ·

CC 4.0 BY-SA版权

本文介绍Java数据库连接(JDBC)的基本概念及使用方法，包括连接数据库、执行SQL语句、处理结果集等步骤，并提供示例代码。

JDBC Java Database connectivity

Java数据库连接规范(一套接口) Sun公司提供的

四个核心类

DriverManager 创建连接

Connection 连接类

Statement 执行sql语句

ResultSet 结果集

JDBC连接步骤

1.注册驱动

2.获取连接 Connection

3.获取sql语句的执行对象 Statement

4.执行sql语句返回结果集 ResultSet

5.处理结果集

6.关闭资源

1.注册驱动

//这种注册方式相当于注册了两遍

//Driver类内部的静态代码块已经注册了一遍

//DriverManager.registerDriver(new Driver());

//注册驱动

//直接把该类加载内存当中参数是全限定类名包名+类名

Class.forName("com.mysql.jdbc.Driver");

2.获取连接

//获取连接对象方式一

Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/myjdbc","root", "123456");

//获取连接的方式二

Properties info = new Properties();

//添加用户名，密码

info.setProperty("user","root");

info.setProperty("password","123456");

Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/myjdbc", info);

//获取连接方式三相当于使用了一个get请求携带参数，访问链接

String url = "jdbc:mysql://localhost:3306/myjdbc?user=root&password=123456";

Connection conn = DriverManager.getConnection(url);

3.获取执行sql语句的对象

Statement statement = conn.createStatement();

4.执行sql语句返回结果集

//字段查询语句结果集中添加的索引要和查询语句中的字段对应

String sql = "select * from users";

ResultSet resultset = statement.executeQuery(sql);

5.处理结果集

//循环遍历结果集输出结果

//有记录 next()方法返回true 反之

while(resultset.next()){

//打印数据

//注意：查询数据库时，索引从1开始

System.out.println(resultset.getObject(1));

System.out.println(resultset.getObject(2));

}

6.关闭资源

resultset.close();

statement.close();

conn.close();

增删改查

@Test

public void testInsert() throws Exception {

Class.forName("com.mysql.jdbc.Driver");

String url = "jdbc:mysql://localhost:3306/myjdbc?user=root&password=123456";

Connection connection = DriverManager.getConnection(url);

Statement statement = connection.createStatement();

String sql = "insert into users values(5, 'jianzhong', '123123', '123123@qq.com', '2018-03-22')";

int row = statement.executeUpdate(sql);

System.out.println(row);

if (row > 0) {

System.out.println("插入成功");

}

statement.close();

connection.close();

}

@Test

public void testUpdate() throws Exception {

Class.forName("com.mysql.jdbc.Driver");

String url = "jdbc:mysql://localhost:3306/myjdbc?user=root&password=123456";

Connection connection = DriverManager.getConnection(url);

Statement statement = connection.createStatement();

String sql = "update users set name='xuesheng'";

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?user=root&password=123456";

Connection connection = DriverManager.getConnection(url);

Statement statement = connection.createStatement();

// 查询

String sql = "select id, name, email from users";

ResultSet resultSet = statement.executeQuery(sql);

// 处理结果集 while (resultSet.next()) {

// 可以直接填字段名称

system.out.print(resultSet.getObject("id") + " ");

System.out.print(resultSet.getObject("name") + " ");

System.out.println(resultSet.getObject("email") + " ");

}

resultSet.close();

statement.close();

connection.close();

}

@Test

public void testDelete() throws Exception {

Class.forName("com.mysql.jdbc.Driver");

String url = "jdbc:mysql://localhost:3306/myjdbc?user=root&password=123456";

Connection connection = DriverManager.getConnection(url);

Statement statement = connection.createStatement();

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();

连接数据库的异常处理

public class DemoException {

public static void main(String[] args) {
Connection conn = null;
Statement stat = null;
ResultSet resu = null;
try {
Class.forName("com.mysql.jdbc.Driver");
String url = "jdbc:mysql://localhost:3306/myjdbc";
conn = DriverManager.getConnection(url,"root","123456");
stat = conn.createStatement();
String sql = "select * from users";
resu = stat.executeQuery(sql);
//处理结果集(把数据的记录封装到对象中)
//把对象保存到数组当中并遍历打印
List<User> u = new ArrayList<>();
while(resu.next()){
//创建对象
User user = new User();
user.setId(resu.getInt("id"));
user.setName(resu.getString("name"));
user.setPassword(resu.getString("password"));
user.setEmail(resu.getString("email"));
user.setBrithday(resu.getDate("brithday"));
//放入集合中
u.add(user);
}
//遍历查看
for(User user:u){
System.out.println(user);
}

} catch (ClassNotFoundException e) {
//停止程序
throw new RuntimeException("驱动加载失败");
}catch(SQLException e){
throw new RuntimeException("");
}finally{
//关闭资源前要做非空判断，防止空指针出现
if(resu != null){
try{
resu.close();
}catch(SQLException e){
throw new RuntimeException("关闭失败");
}
//加快系统回收的速度
resu = null;
}
if(stat != null){
try{
stat.close();
}catch(SQLException e){
throw new RuntimeException("关闭失败");
}
//加快系统回收的速度
stat = null;
}
if(conn != null){
try{
conn.close();
}catch(SQLException e){
throw new RuntimeException("关闭失败");
}
//加快系统回收的速度
conn = null;
}

}

}
}