JDBC的连接

  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;
}

}

}
}

JDBC工具类

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

}

    //使用静态代码块加载驱动 读取配置文件

// static{

// Properties properties = new Properties();

// try{

// FileInputStream fis = new FileInputStream("sre/dbinfo.properties");

// properties.load(fis);

// //读文件

// driverClass = properties.getProperty("driverClass");

// url = properties.getProperty("url");

// user = properties.getProperty("user");

// password = properties.getProperty("password");

// }catch(Exception e){

//

// }

// //获取数据库连接的方法

//        try{

//        Class.forName(driverClass);

//        }catch(Exception e){

//        

//        }

// }

// 获取数据库连接方法

public static Connection getConnection() throws ClassNotFoundException, SQLException {

  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;

}

}

}