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; // 返回一条记录

}

}*/