自己动手封装java操作数据库的方法：
一：目录结构
二：所需依赖的第三方jar包
这里只需引入mysql-connector-java-5.1.8-bin.jar，mysql数据库驱动jar包
三：代码
1：和数据库进行交互，首先是数据源，获取连接，代码如下：
/**
*
*/
package com.hlcui.datasource; import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException; /**
* @author Administrator 定义获取和关闭数据源的方法
*/
public class DataSourceUtil { /**
* 注册数据库驱动
*/
static {
try {
Class.forName("com.mysql.jdbc.Driver");
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
} /**
* 获取数据源
*
* @throws SQLException
*/
public static Connection getConnection(String url, String user,
String password) throws SQLException {
return DriverManager.getConnection(url, user, password);
} /**
* 关闭数据源
*
* @throws SQLException
*/
public static void closeConnection(Connection conn) throws SQLException {
if (null != conn) {
conn.close();
}
}
}
2：有个数据库连接之后，可以对数据库进行操作了
/**
*
*/
package com.hlcui.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 com.hlcui.Constants;
import com.hlcui.datasource.DataSourceUtil;
import com.hlcui.entity.Student; /**
* @author Administrator
*
*/
public class DBUtil { /**
* 查询所有学生信息
*
* @return
*/
public static List<Student> getAllStuInfo() {
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
List<Student> stus = new ArrayList<Student>();
try {
conn = DataSourceUtil.getConnection(Constants.URL,
Constants.USERNAME, Constants.PASSWORD);
String sql = "select * from student";
ps = conn.prepareStatement(sql);
rs = ps.executeQuery();
while (rs.next()) {
int id = rs.getInt("id");
String name = rs.getString("name");
int age = rs.getInt("age");
double score = rs.getDouble("score");
Student s = new Student(id, name, age, score);
stus.add(s);
} } catch (Exception e) {
e.printStackTrace();
} finally { try {
if (null != rs) {
rs.close();
}
if (null != ps) {
ps.close();
}
if (null != conn) {
DataSourceUtil.closeConnection(conn);
}
} catch (SQLException e) {
e.printStackTrace();
} }
return stus;
} /**
* 根据id查询学生的信息
*/
public static Student getStuInfoById(int id) {
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
Student s = null;
try {
conn = DataSourceUtil.getConnection(Constants.URL,
Constants.USERNAME, Constants.PASSWORD);
String sql = "SELECT * FROM student where id = ?";
ps = conn.prepareStatement(sql);
ps.setInt(1, id);
rs = ps.executeQuery();
while (rs.next()) {
String name = rs.getString("name");
int age = rs.getInt("age");
double score = rs.getDouble("score");
s = new Student(id, name, age, score);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
if (null != rs) {
rs.close();
}
if (null != ps) {
ps.close();
}
if (null != conn) {
DataSourceUtil.closeConnection(conn);
}
} catch (Exception e2) {
}
}
return s;
} /**
* 增加学生信息
*/
public static void saveStuInfo(Student stu) {
Connection conn = null;
PreparedStatement ps = null;
try {
conn = DataSourceUtil.getConnection(Constants.URL,
Constants.USERNAME, Constants.PASSWORD);
String sql = "insert into student (id,name,age,score) values (?,?,?,?)";
ps = conn.prepareStatement(sql);
ps.setInt(1, stu.getId());
ps.setString(2, stu.getName());
ps.setInt(3, stu.getAge());
ps.setDouble(4, stu.getScore());
int insertCount = ps.executeUpdate();
System.out.println(isSuccess(insertCount));
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
if (null != ps) {
ps.close();
}
if (null != conn) {
conn.close();
}
} catch (Exception e2) {
e2.printStackTrace();
}
}
} /**
* 根据id删除学生信息
*/
public static void deleteStuInfo(int id) {
Connection conn = null;
PreparedStatement ps = null;
try {
conn = DataSourceUtil.getConnection(Constants.URL,
Constants.USERNAME, Constants.PASSWORD);
String sql = "delete from student where id = ?";
ps = conn.prepareStatement(sql);
ps.setInt(1, id);
int deleteCount = ps.executeUpdate();
System.out.println(isSuccess(deleteCount));
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
if (null != ps) {
ps.close();
}
if (null != conn) {
conn.close();
}
} catch (Exception e2) {
e2.printStackTrace();
}
}
} /**
* 根据id修改学生信息
*/
public static void modifyStuInfo(Student stu) {
Connection conn = null;
PreparedStatement ps = null;
try {
conn = DataSourceUtil.getConnection(Constants.URL,
Constants.USERNAME, Constants.PASSWORD);
String sql = "update student set name = ?,age = ? ,score = ? where id = ?";
ps = conn.prepareStatement(sql);
ps.setString(1, stu.getName());
ps.setInt(2, stu.getAge());
ps.setDouble(3, stu.getScore());
ps.setInt(4, stu.getId());
int count = ps.executeUpdate();
System.out.println(isSuccess(count));
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
if (null != ps) {
ps.close();
}
if (null != conn) {
conn.close();
}
} catch (Exception e2) {
e2.printStackTrace();
}
}
} /**
* 判断操作是否成功
*/
public static String isSuccess(int count) {
if (count > 0) {
return "操作成功！";
} else {
return "操作失败！";
}
}
}
3：POJO实体类
/**
*
*/
package com.hlcui.entity; /**
* @author Administrator
*
*/
public class Student {
private int id;
private String name;
private int age;
private double score; public Student() { } public Student(int id, String name, int age, double score) {
super();
this.id = id;
this.name = name;
this.age = age;
this.score = score;
} public int getId() {
return id;
} public void setId(int id) {
this.id = id;
} public String getName() {
return name;
} public void setName(String name) {
this.name = name;
} public int getAge() {
return age;
} public void setAge(int age) {
this.age = age;
} public double getScore() {
return score;
} public void setScore(double score) {
this.score = score;
} }
4：常量类Constants
/**
*
*/
package com.hlcui; /**
* @author Administrator
*
*/
public class Constants { public static final String URL = "jdbc:mysql://localhost:3306/test";
public static final String USERNAME = "root";
public static final String PASSWORD = "root"; }
四：测试类，验证是否能够操作数据库中数据
/**
*
*/
package com.hlcui.test; import java.sql.Connection;
import java.sql.SQLException;
import java.util.Iterator;
import java.util.List; import org.junit.Test; import com.hlcui.Constants;
import com.hlcui.dao.DBUtil;
import com.hlcui.datasource.DataSourceUtil;
import com.hlcui.entity.Student; /**
* @author Administrator
*
*/
public class TestJdbc { /**
* 测试获取数据库连接
*
* @throws SQLException
*/
@Test
public void testGetConnection() throws SQLException {
Connection conn = DataSourceUtil.getConnection(Constants.URL,
Constants.USERNAME, Constants.PASSWORD);
System.out.println("conn:" + conn); } /**
* 测试获取所有的学生信息
*/
@Test
public void testGetAllStuInfo() {
List<Student> stus = DBUtil.getAllStuInfo();
Iterator<Student> it = stus.iterator();
while (it.hasNext()) {
Student s = it.next();
System.out.println(s.getId() + "," + s.getName() + "," + s.getAge()
+ "," + s.getScore());
}
} /**
* 测试根据id获取学生信息
*/
@Test
public void testGetStuInfoById() {
int id = 1;
Student s = DBUtil.getStuInfoById(id);
System.out.println(s.getId() + "," + s.getName() + "," + s.getAge()
+ "," + s.getScore());
} /**
* 测试添加学生信息
*/
@Test
public void testSaveStuInfo() {
Student s = new Student(4, "Lucy", 27, 100.0);
DBUtil.saveStuInfo(s);
} /**
* 测试删除学生信息
*/
@Test
public void testDeleteStuInfo() {
int id = 4;
DBUtil.deleteStuInfo(id);
} /**
* 测试修改学生信息
*/
@Test
public void testModifyStuInfo(){
Student s = new Student(3,"Lili",24,9000.0);
DBUtil.modifyStuInfo(s);
} }
以上代码均已验证正确。