JDBC的四个步骤和代码实现

JDBC是从JAVA中连接MySQL数据库的驱动,我们可以使用编程语言来实现它,其实它的实现是固定的,只用按照这个思路和步骤就可以了。
实现分为下面四个步骤:

1.注册驱动
2.获得连接对象
3.获取statement对象
4.释放资源

因为操作数据库无外乎增删改查这些,如果单单每一种操作都重新写代码的话,会显得冗余复杂,太LOW了,所以可以适当的对代码进行优化,具体的实现如下:

学生类

package cn.edu360.entity;

public class Student {

private Integer id;
private String name;
private String gender;
private int age;

public Student() {
}

public Student(Integer id, String name, String gender, int age) {
super();
this.id = id;
this.name = name;
this.gender = gender;
this.age = age;
}

@Override
public String toString() {
return “Student [id=” + id + “, name=” + name + “, gender=” + gender + “, age=” + age + “]”;
}

public Integer getId() {
return id;
}

public void setId(Integer id) {
this.id = id;
}

public String getName() {
return name;
}

public void setName(String name) {
this.name = name;
}

public String getGender() {
return gender;
}

public void setGender(String gender) {
this.gender = gender;
}

public int getAge() {
return age;
}

public void setAge(int age) {
this.age = age;
}

}

将四个步骤中的1、2、4封装成cn.edu360.utils如下:

utils

package cn.edu360.utils;

import .sql.Connection;
import .sql.DriverManager;
import .sql.ResultSet;
import .sql.SQLException;
import .sql.Statement;

/*
* JDBC四个步骤
*/
public class JDBCUtils {

private static String driverName = “com.mysql.jdbc.Driver”;
private static String jdbc_url = “jdbc:mysql://localhost:3306/store”;
private static String username = “root”;
private static String password = “12580”;

// 1.注册驱动,只需要注册一次即可(在静态代码中执行一次即可)
static {
try {
Class.forName(driverName);
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}

// 2.获得连接对象,以后需要多次获得
public static Connection getConnection() {
try {
return DriverManager.getConnection(jdbc_url, username, password);
} catch (SQLException e) {
e.printStackTrace();
}
return null;
}

// 3.获取statement对象,需要反复执行,不需要定义这个方法了

// 4.释放资源,需要反复执行的(使用方法的重载来定义两个不同参数列表的释放方法)
public static void release(Statement stmt, Connection connection) {
if (stmt != null) {
try {
stmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
stmt = null;
}

if (connection != null) {
try {
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
connection = null;
}
}

public static void release(ResultSet rs, Statement stmt, Connection connection) {
if (rs != null) {
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
rs = null;
}

if (stmt != null) {
try {
stmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
stmt = null;
}

if (connection != null) {
try {
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
connection = null;
}
}

}

增删改查封装成cn.edu360.dao如下:

增删改查

package cn.edu360.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import cn.edu360.entity.Student;
import cn.edu360.utils.JDBCUtils;

public class StudentDao {

/**
* 添加数据到MySQL中
*
* @param student
*/
public void save(Student student) {
// 获得connection
Connection connection = JDBCUtils.getConnection();
PreparedStatement prepareStatement = null;

// 创建Statement对象
// 定义一个sql
String sql = “INSERT INTO student VALUES (?, ?, ?, ?)”;
try {
prepareStatement = connection.prepareStatement(sql);

prepareStatement.setInt(1, student.getId());
prepareStatement.setString(2, student.getName());
prepareStatement.setString(3, student.getGender());
prepareStatement.setInt(4, student.getAge());
// 执行sql语句
prepareStatement.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
}

// 释放资源
JDBCUtils.release(prepareStatement, connection);
}

/**
* 根据ID号删除数据
*
* @param id
*/
public void deleteById(Integer id) {
// 获得connection
Connection connection = JDBCUtils.getConnection();
PreparedStatement prepareStatement = null;

// 创建Statement对象
// 定义一个sql
String sql = “DELETE FROM student WHERE id = ?”;
try {
prepareStatement = connection.prepareStatement(sql);
prepareStatement.setInt(1, id);
// 执行sql语句
prepareStatement.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
}
// 释放资源
JDBCUtils.release(prepareStatement, connection);
}

/**
* 更新学生信息
*
* @param student
*/
public void update(Student student) {
// 获得connection
Connection connection = JDBCUtils.getConnection();
PreparedStatement prepareStatement = null;

// 创建Statement对象
// 定义一个sql
String sql = “UPDATE student SET name = ? , gender = ? , age = ? WHERE id = ?”;
try {
prepareStatement = connection.prepareStatement(sql);
prepareStatement.setString(1, student.getName());
prepareStatement.setString(2, student.getGender());
prepareStatement.setInt(3, student.getAge());
prepareStatement.setInt(4, student.getId());
// 执行sql语句
prepareStatement.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
}
// 释放资源
JDBCUtils.release(prepareStatement, connection);
}

/**
* 根据ID号查找学生信息
*
* @param id
* @return
*/
public Student getById(Integer id) {
Student s = null;
// 获得connection
Connection connection = JDBCUtils.getConnection();
PreparedStatement prepareStatement = null;
ResultSet rs =null;
// 创建Statement对象
// 定义一个sql
String sql = “SELECT * FROM student WHERE id = ?”;
try {
prepareStatement = connection.prepareStatement(sql);
prepareStatement.setInt(1,id);
// 执行sql语句
rs = prepareStatement.executeQuery();
// 从rs中得到数据
rs.next();
Integer sid = rs.getInt(1);
String name = rs.getString(2);
String gender = rs.getString(3);
int age = rs.getInt(4);
s = new Student();
s.setId(sid);
s.setName(name);
s.setGender(gender);
s.setAge(age);
} catch (SQLException e) {
e.printStackTrace();
}

// 释放资源
JDBCUtils.release(rs,prepareStatement, connection);
return s;
}

/**
* 查找所有学生信息
*
* @return
*/
public List<Student> findAll() {
ArrayList<Student> list = new ArrayList<Student>();
// 获得connection
Connection connection = JDBCUtils.getConnection();
PreparedStatement prepareStatement = null;
ResultSet rs = null;
// 创建Statement对象
// 定义一个sql
String sql = “SELECT * FROM student”;
try {
prepareStatement = connection.prepareStatement(sql);
// 执行sql语句
rs = prepareStatement.executeQuery();
while (rs.next()) {
// 从rs中得到数据
Integer sid = rs.getInt(1);
String name = rs.getString(2);
String gender = rs.getString(3);
int age = rs.getInt(4);
Student s = new Student();
s.setId(sid);
s.setName(name);
s.setGender(gender);
s.setAge(age);
list.add(s);
}
} catch (SQLException e) {
e.printStackTrace();
}
// 释放资源
JDBCUtils.release(rs,prepareStatement, connection);
return list;
}
}

测试代码如下:

测试代码

package cn.edu360.test;

import java.util.List;

import org.junit.Test;

import cn.edu360.dao.StudentDao;
import cn.edu360.entity.Student;

public class TestDao {

@Test
public void testSave() {
StudentDao dao = new StudentDao();
Student s = new Student(11, “tom”, “f”, 20);
dao.save(s);
}

@Test
public void testDelete() {
StudentDao dao = new StudentDao();
dao.deleteById(11);
}

@Test
public void testUpdate() {
StudentDao dao = new StudentDao();
Student s = new Student(1, “zhaozhoa”, “m”, 18);
dao.update(s);
}

@Test
public void testGetById() {
StudentDao dao = new StudentDao();
Student s = dao.getById(2);
System.out.println(s);
}

@Test
public void testFindAll() {
StudentDao dao = new StudentDao();
List<Student> list = dao.findAll();
for (Student s : list) {
System.out.println(s);
}
}
}

点赞

发表评论

电子邮件地址不会被公开。 必填项已用*标注