初学这个,发一下自己写的东西吧
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Scanner;
import com.pojo.Emp;
public class DatabaseUntils {
//创建数据库的连接信息
private String userName = "root";
private String passWord = "123456";
private String url = "jdbc:mysql://localhost:3306/test?useSSL=true";
private Connection connection;
private Emp emp;
//加载驱动
{
try {
Class.forName("com.mysql.jdbc.Driver");
System.out.println("驱动连接成功");
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
System.out.println("驱动连接失败");
}
}
//连接数据库
public DatabaseUntils() {
try {
this.connection = DriverManager.getConnection(url, userName, passWord);
System.out.println("连接成功");
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
System.out.println("连接失败");
}
}
//插入员工
public void addEmp(Emp emp) {
String sql = "INSERT emp VALUES (?,?,?,?,?,?,?,?)";
PreparedStatement ps = null;
try {
ps = connection.prepareStatement(sql);
ps.setInt(1, emp.getEmpno());
ps.setString(2, emp.getEname());
ps.setString(3, emp.getJob());
ps.setInt(4, emp.getMgr());
ps.setDate(5, emp.getHiredate());
ps.setInt(6, emp.getSal());
ps.setInt(7, emp.getComm());
ps.setInt(8, emp.getDeptno());
int len = ps.executeUpdate();
System.out.println("一共新增"+len+"行");
} catch (SQLException e) {
e.printStackTrace();
}finally {
try {
ps.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
//修改员工
public void updateEmp(String ename,int sal,int comm,int empno) {
String sql = "UPDATE emp SET ename=?, sal=?, comm=? WHERE empno = ?";
PreparedStatement ps = null;
try {
ps = connection.prepareStatement(sql);
ps.setString(1, ename);
ps.setInt(2, sal);
ps.setInt(3, comm);
ps.setInt(4, empno);
ps.executeUpdate();
System.out.println("修改成功");
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
try {
ps.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
//删除员工
public void deleteEmp(int empno) {
String sql = "DELETE FROM emp WHERE empno = ?";
PreparedStatement ps = null;
try {
ps = connection.prepareStatement(sql);
ps.setInt(1, empno);
ps.executeUpdate();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
//查询员工
public Emp selectEmp(int empno) {
String sql = "SELECT * FROM emp WHERE empno = ?";
PreparedStatement ps = null;
ResultSet rs = null;
Emp result = null;
try {
ps = connection.prepareStatement(sql);
ps.setInt(1, empno);
rs = ps.executeQuery();
while(rs.next()) {
result = new Emp();
result.setEmpno(rs.getInt("empno"));
result.setEname(rs.getString("ename"));
result.setJob(rs.getString("job"));
result.setMgr(rs.getInt("mgr"));
result.setHiredate(rs.getDate("hiredate"));
result.setSal(rs.getInt("sal"));
result.setComm(rs.getInt("comm"));
result.setDeptno(rs.getInt("deptno"));
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
if(ps != null) {
try {
ps.close();
rs.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
return result;
}
//查询所有员工信息
public ArrayList<Emp> selectAllEmp() {
String sql = "SELECT * FROM emp";
PreparedStatement ps = null;
ResultSet set =null;
ArrayList<Emp> results = null;
try {
ps = connection.prepareStatement(sql);
set = ps.executeQuery();
while(set.next()) {
if(results == null) {
results = new ArrayList<Emp>();
}
Emp result = new Emp();
result.setEmpno(set.getInt("empno"));
result.setEname(set.getString("ename"));
result.setJob(set.getString("job"));
result.setMgr(set.getInt("mgr"));
result.setHiredate(set.getDate("hiredate"));
result.setSal(set.getInt("sal"));
result.setComm(set.getInt("comm"));
result.setDeptno(set.getInt("deptno"));
results.add(result);
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
try {
set.close();
ps.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
return results;
}
//关闭连接
public void closeConnection() {
if(this.connection != null) {
try {
this.connection.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
}