java 底层数据库操作开发 java数据库编程实例

importjava.sql.Connection;importjava.sql.PreparedStatement;importjava.sql.ResultSet;importjava.sql.SQLException;importjava.sql.Statement;importjava.util.ArrayList;importjava.util.List;importjava.util.Vector;public classDbOperation {private static DbOperation instance = null;public static DbOperation getInstance() { //返回DbOperation类实例的静态方法,单例模式！！！！
if (instance == null) {
instance= newDbOperation();
}returninstance;
}public boolean addTbNews(TbNews tns) { //添加数据方法
Connection conn=null;boolean result=false;try{
conn=DbTool.getConn(); //建立与数据库的连接
String sqlInsert="INSERT INTO tbNews(topicId,contentTxt)VALUES(?,?)";
PreparedStatement stm=conn.prepareStatement(sqlInsert);
stm.setString(1, tns.getTopicId());
stm.setString(2, tns.getContentTxt());int i=stm.executeUpdate();if (i==1) {
result=true;
}
}catch(SQLException e) {
e.printStackTrace();
}finally{try{
conn.close();
}catch(SQLException e) {
e.printStackTrace();
}
}returnresult;
}public Vector queryTbNews(){ //返回Vector类型方式查询数据
Vector list =new Vector();
Connection conn=null;try{
conn=DbTool.getConn();
Statement stmt=conn.createStatement();
String querySql="select * from TbNews";
ResultSet rs=stmt.executeQuery(querySql);while(rs.next()) {
TbNews tbs=newTbNews();
tbs.setTopicId(rs.getString(1));
tbs.setContentTxt(rs.getString(2));
list.add(tbs);
}
}catch(Exception e) {
e.printStackTrace();
}finally{try{
conn.close();
}catch(SQLException e) {
e.printStackTrace();
}
}returnlist;
}public List selectTbNews(){ //查询数据方法
List tbsList=new ArrayList();
Connection conn=null;try{
conn=DbTool.getConn();
Statement stmt=conn.createStatement();
String selectSql="select * from TbNews";
ResultSet rs=stmt.executeQuery(selectSql); //执行SQL并返回结果集
while(rs.next()) {
String tbs=rs.getString("topicId")+","+rs.getString("contentTxt");
tbsList.add(tbs);
}
}catch(Exception e) {
e.printStackTrace();
}finally{try{
conn.close();//关闭连接
}catch(SQLException e) {
e.printStackTrace();
}
}returntbsList;
}
}/*package 数据库_向数据库插入数据;
import java.sql.Connection;
import java.sql.DriverManager;
public class DatabaseConnection {
private static Connection conn = null;
public static Connection getCon() {
try {
Class.forName("com.mysql.jdbc.Driver"); //加载数据库连接驱动
String user = "root";
String psw = "XXX"; //XXX为自己的数据库的密码
String url = "jdbc:mysql://localhost:3306/ZZZ"; //ZZZ为连接的名字
conn = DriverManager.getConnection(url, user, psw); //获取连接
} catch (Exception e) {
System.out.println("连接数据库失败");
e.printStackTrace();
}
return conn;
}
}
package 数据库_向数据库插入数据;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
//EmployeeOperation类用于操作数据库，以下的编写方法是JAVA软件编程的一种设计模式，称为！！！！！ 单例模式，！！！！！！！
//方法中先判断该类的对象是否为空，只有为空才创建(new) ,因此保证该对象在程序中永远是唯一的，可以避免重复创建对象造成的系统内存被过多占用
public class EmployeeOperation {
private static EmployeeOperation instance = null;
public static EmployeeOperation getInstance() { //返回EmployeeOperation类实例的静态方法,单例模式！！！！
if (instance == null) {
instance = new EmployeeOperation();
}
return instance;
}
public boolean saveEmployee(Employee emp) { //向数据库中加入数据
boolean result = false;
Connection conn = null;
try {
conn = DatabaseConnection.getCon(); //建立数据库连接
String sqlInset = "insert into company.tb_employee(empId, empName, empAge, empSex) values(?, ?, ?, ?)";
PreparedStatement stmt = conn.prepareStatement(sqlInset); //会抛出异常
stmt.setInt(1, emp.getEmpId()); //设置SQL语句第一个“？”的值
stmt.setString(2, emp.getEmpName()); //设置SQL语句第二个“？”的值
stmt.setInt(3, emp.getEmpAge()); //设置SQL语句第三个“？”的值
stmt.setString(4, emp.getEmpSex()); //设置SQL语句第四个“？”的值
int i = stmt.executeUpdate(); //执行插入数据操作，返回影响的行数
if (i == 1) {
result = true;
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally { //finally的用处是不管程序是否出现异常，都要执行finally语句，所以在此处关闭连接
try {
conn.close(); //打开一个Connection连接后，最后一定要调用它的close()方法关闭连接，以释放系统资源及数据库资源
} catch(SQLException e) {
e.printStackTrace();
}
}
return result;
}
public List selectEmployee() { //从数据库中查询所需数据
List empList = new ArrayList();
Connection conn = null;
try {
conn = DatabaseConnection.getCon();
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery("select * from company.tb_employee");//执行SQL并返回结果集
while (rs.next()) {
Employee emp = new Employee();
emp.setEmpId(rs.getInt("empId")); //从结果集rs中获取内容时，若为字符串类型的，用rs.getString("string")方法
emp.setEmpName(rs.getString("empName")); //其中str为想要从 数据库的 表 中获取的信息
emp.setEmpAge(rs.getInt("empAge")); //若为int类型，用rs.getInt(number);
emp.setEmpSex(rs.getString("empSex"));
empList.add(emp);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
conn.close(); //关闭连接
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
return empList; //返回结果
}
public boolean updateEmployee(Employee emp) { //根据员工的编号更改员工的年龄信息
boolean result = false;
Connection conn = null;
try {
conn = DatabaseConnection.getCon();
String sql = "update company.tb_employee set empAge=? where empId=?"; //update语句
PreparedStatement stmt = conn.prepareStatement(sql);
stmt.setInt(1, emp.getEmpAge()); //设置SQL语句第一个"?"的参数值
stmt.setInt(2, emp.getEmpId()); //设置SQL语句第二个"?"的参数值
int flag = stmt.executeUpdate(); //执行修改操作，返回影响的行数
if (flag == 1) { //修改成功返回true
result = true;
}
} catch(Exception e) {
e.printStackTrace();
} finally {
try {
conn.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
return result;
}
public boolean deleteEmployeeById(Employee emp) {
boolean result = false;
Connection conn = null;
try {
conn = DatabaseConnection.getCon();
String sql = "delete from company.tb_employee where empId = ?";
PreparedStatement stmt = conn.prepareStatement(sql);
stmt.setInt(1, emp.getEmpId());
int i = stmt.executeUpdate();
if (i == 1) {
result = true;
}
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
conn.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
return result;
}
}
package 数据库_向数据库插入数据;
public class MainTest {
public static void main(String[] args) { //测试向数据库的表中插入元素的方法
Employee emp = new Employee();
emp.setEmpId(2);
emp.setEmpName("LILEI");
emp.setEmpAge(33);
emp.setEmpSex("male");
boolean res = EmployeeOperation.getInstance().saveEmployee(emp);
if (res == true) {
System.out.println("向company.tb_employee表中插入数据成功");
} else {
System.out.println("向company.tb_employee表中插入数据失败");
}
}
}
package 数据库_向数据库插入数据;
import java.util.List;
public class SelectMainTest { //测试从数据库中获取数据的方法
public static void main(String[] args) {
List empList = EmployeeOperation.getInstance().selectEmployee();
System.out.println("员工ID\t员工姓名\t员工年龄\t员工性别");
for (Employee emp : empList) {
System.out.print(emp.getEmpId() + "\t" + emp.getEmpName() + "\t" + emp.getEmpAge() + "\t" + emp.getEmpSex());
System.out.println();
}
}
}
package 数据库_向数据库插入数据;
import java.util.List;
public class UpdateMainTest { //根据员工的id修改员工年龄的方法
public static void main(String[] args) {
List empList = EmployeeOperation.getInstance().selectEmployee();
System.out.println("员工ID");
for (Employee emp : empList) {
System.out.println(emp.getEmpId());
}
Employee emp = new Employee();
emp.setEmpId(2);
emp.setEmpAge(50);
boolean res = EmployeeOperation.getInstance().updateEmployee(emp);
if (res == true) {
System.out.println("编号为2的员工的年龄修改成功");
} else {
System.out.println("编号为2的员工的年龄修改失败");
}
}
}
package 数据库_向数据库插入数据;
public class DeleteMainTest {
public static void main(String[] args) { //测试删除对应id的员工的方法
Employee emp = new Employee();
emp.setEmpId(1);
boolean res = EmployeeOperation.getInstance().deleteEmployeeById(emp);
if (res == true) {
System.out.println("成功删除id为1的员工");
} else {
System.out.println("未能成功删除id为1的员工");
}
}
}
//
对于ResultSet取得结果后，我们可以使用Vector二维数据存储，然后再利用new DefaultTableModel(vector，vector)来构造表格模型
Vector> list = new Vector>();
while(result.next())
{
Vector vTemp = new Vector();
vTemp.add(result.getInt(1));//ID
vTemp.add(result.getString(2));//姓名
vTemp.add(result.getInt(3));//队
vTemp.add(result.getString(4));//手机
if(result.getInt(5) == 0){
vTemp.add("否");//是否已经缴费
}
else{
vTemp.add("是");//是否已经缴费
}
vTemp.add(result.getInt(6));//欠费
list.add(vTemp);
}
Vector columns = new Vector();
columns.add("ID");
columns.add("姓名");
columns.add("队");
columns.add("手机");
columns.add("已缴费");
columns.add("欠费");
tableModel = new DefaultTableModel(list,columns);
table.setModel(tableModel);
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.Vector;
import javax.swing.JOptionPane;
import javax.swing.JTable;
import javax.swing.table.DefaultTableModel;
public class Utilities {
// 功能：实现结果集的表格显示
public static void displayResultSet(JTable table, ResultSet rs)
throws SQLException {
//
rs.beforeFirst();// 指针移到第一条记录前面
boolean hasRecords = rs.next();
if (!hasRecords) { // 记录集为空，提示一条消息
JOptionPane.showMessageDialog(table, "无相关记录", "Check your input!",
JOptionPane.ERROR_MESSAGE);
return;
}
Vector columnHeads = new Vector();// 用于存储表头字段(列名)
Vector rows = new Vector();// 用于存储记录行
try {
// 获取字段的名称
ResultSetMetaData rsmd = rs.getMetaData();
for (int i = 1; i <= rsmd.getColumnCount(); ++i)
columnHeads.addElement(rsmd.getColumnName(i));
do {// 获取记录集
rows.addElement(getNextRow(rs, rsmd));
} while (rs.next());
// 建立相应的TableModel,并将TableModel应用到Table中显示出来
DefaultTableModel model = new DefaultTableModel(rows, columnHeads);
table.setModel(model);
return;
} catch (SQLException exc) {
JOptionPane.showMessageDialog(table, exc.toString(),
"Check your input!", JOptionPane.ERROR_MESSAGE);
return;
}
}
//
// 被displayResultSet(JTable table, ResultSet rs)调用, 以Vector形式返回一个记录行
private static Vector getNextRow(ResultSet rs, ResultSetMetaData rsmd)
throws SQLException {
Vector currentRow = new Vector();
for (int i = 1; i <= rsmd.getColumnCount(); ++i)
currentRow.addElement(rs.getString(i));
return currentRow; // 返回一条记录
}
}*/