package sun;
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.Date;
import java.util.HashSet;
import java.util.Iterator;
import java.util.List;
import java.util.Set;
import com.mysql.jdbc.Statement;
import sun.util.ConnectionUtil;
import vo.Emp;
public class TestMysql {
public static void main(String[] args) throws Exception {
Emp emp=new Emp(“李四”,“职员”,7788,10.00,900.00,new Date(),40);
System.out.println(insertEmp(emp));
}
public static boolean insertEmp() throws Exception {
// 取得Connection连接对象
Connection conn = ConnectionUtil.getConnection();
// 定义出sql语句
String sql = "INSERT INTO emp(empno,ename,job,sal,hiredate,mgr,comm,deptno)"
+ "VALUES(1006,'李四','总裁',9000.00,NOW(),7788,2000.00,10)";
// 取得发送sql语句的对象
PreparedStatement pst = conn.prepareStatement(sql);
// 执行sql语句
int row = pst.executeUpdate();
ConnectionUtil.close(conn);
return row > 0;
}
public static boolean updateDeposit(Double sum) throws SQLException {
// 取得连接对象
Connection conn = ConnectionUtil.getConnection();
// 创建sql语句
String sql1 = "UPDATE emp SET deposit=deposit-" + sum + "WHERE ename='SMITH'";
String sql2 = "UPDATE emp SET deposit=deposit+" + sum + "WHERE ename='ALLEN'";
// 取消事物的自动提交
conn.setAutoCommit(false);
int row1 = 0;
int row2 = 0;
try {
// 取得预编译对象
PreparedStatement pst1 = conn.prepareStatement(sql1);
PreparedStatement pst2 = conn.prepareStatement(sql2);
// 执行sql语句
row1 = pst1.executeUpdate(sql1);
row2 = pst2.executeUpdate(sql2);
// 把之前的代码全部提交
conn.commit();
} catch (Exception e) {
e.printStackTrace();
// 如果其中有代码出现了异常则事物回滚
conn.rollback();
} finally {
// 关闭连接
ConnectionUtil.close(conn);
}
return row1 > 0 && row2 > 0;
}
/**
* 根据编号删除数据
*
* @param id 要删除的数据的编号
* @return
* @throws Exception
*/
public static boolean deleteById(Integer id) throws Exception {
Connection conn = ConnectionUtil.getConnection();
String sql = "DELETE FROM emp WHERE empno=" + id;
// 取得预编译对象
PreparedStatement pst = conn.prepareStatement(sql);
// 执行sql语句
int row = pst.executeUpdate();
// 关闭连接
ConnectionUtil.close(conn);
return row > 0;
}
/**
* 实现数据的批量删除
*
* @param ids 保存了我们要删除的数据的编号的集合
* @return 如果删除成功返回true 否则返回false
* @throws Exception
*/
public static boolean deleteBatch(Set<Integer> ids) throws Exception {
// 取得连接
Connection conn = ConnectionUtil.getConnection();
// 准备sql语句
StringBuffer sql = new StringBuffer("DELETE FROM emp WHERE empno IN(");
Iterator<Integer> iter = ids.iterator();
while (iter.hasNext()) {
sql.append(iter.next() + ",");
}
// 删除最后一个逗号
sql.delete(sql.length() - 1, sql.length());
// 添加一个右括号
sql.append(")");
// 取得预编译对象
PreparedStatement pst = conn.prepareStatement(sql.toString());
// 执行sql语句
int row = pst.executeUpdate();
ConnectionUtil.close(conn);
return row > 0;
}
/**
* 根据编号查询数据
*
* @param id 要查询的数据的编号
* @throws Exception
*/
public static void selectByID(Integer id) throws Exception {
// 取得连接对象
Connection conn = ConnectionUtil.getConnection();
// 准备sql语句
try {
String sql = "SELECT empno,ename,job,hiredate,sal,comm,mgr,deptno FROM emp WHERE empno=" + id;
// 取得预编译对象
PreparedStatement pst = conn.prepareStatement(sql);
// 执行sql语句
ResultSet rst = pst.executeQuery();
if (rst.next()) {
System.out.println("雇员编号:" + rst.getObject(1) + ",员工姓名:" + rst.getObject(2) + ",职位:" + rst.getObject(3)
+ ",入职日期:" + rst.getObject(4) + ",薪资:" + rst.getObject(5) + ",佣金:" + rst.getObject(6) + ",领导编号:"
+ rst.getObject(7) + ",部门编号:" + rst.getObject(8));
}
} catch (Exception e) {
e.printStackTrace();
} finally {
ConnectionUtil.close(conn);
}
}
/**
* 实现模糊分页查询
* @param kw 模糊查询关键字
* @param cp 当前页
* @param ls 每页显示的数据
* @throws Exception
*/
public static void selectSplitAll(String kw,Integer cp,Integer ls) throws Exception{
if(kw==null) {
kw="";
}
kw="'%"+kw+"%'";
//取得连接对象
Connection conn=ConnectionUtil.getConnection();
//准备sql语句
String sql="SELECT empno,ename,job,hiredate,sal,comm,mgr,deptno FROM emp"+
" WHERE ename LIKE "+kw+" LIMIT "+(cp-1)*ls+","+ls;
//取得预编译对象
PreparedStatement pst=conn.prepareStatement(sql);
//执行sql语句
ResultSet rst=pst.executeQuery();
while(rst.next()) {
System.out.println("雇员编号:" + rst.getObject(1) + ",员工姓名:" + rst.getObject(2) + ",职位:" + rst.getObject(3)
+ ",入职日期:" + rst.getObject(4) + ",薪资:" + rst.getObject(5) + ",佣金:" + rst.getObject(6) + ",领导编号:"
+ rst.getObject(7) + ",部门编号:" + rst.getObject(8));
//System.out.println("雇员编号:" + rst.getObject("empno") + ",员工姓名:" + rst.getObject("ename") + ",职位:" + rst.getObject("job")
// + ",入职日期:" + rst.getObject("hiredate") + ",薪资:" + rst.getObject("sal") + ",佣金:" + rst.getObject("comm") + ",领导编号:"
// + rst.getObject("mgr") + ",部门编号:" + rst.getObject("deptno"));
}
//关闭连接
rst.close();
pst.close();
ConnectionUtil.close(conn);
}
/**
* 增加雇员信息的方法,取得自动增长的主键值
* @return
* @throws SQLException
*/
public static boolean insertEmp1() throws SQLException{
//取得Connection连接对象
Connection conn=ConnectionUtil.getConnection();
//准备sql语句
String sql="INSERT INTO emp(ename,job,sal,hiredate,mgr,comm,deptno)"
+" VALUES('李四','总裁',9000.00,NOW(),7788,2000.00,10)";
//取得预编译对象
PreparedStatement pst=conn.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);
//执行sql语句
int row=pst.executeUpdate();
//取得自动增长的主键值
ResultSet rst=pst.getGeneratedKeys();
if(rst.next()) {
System.out.println(rst.getObject(1));
}
ConnectionUtil.close(conn);
return row>0;
}
/**
* 实现登陆
* @param name 用户名
* @param password 密码
* @return
*/
public static boolean selectLogin(String name,String password) {
//取得Connection对象
Connection conn=ConnectionUtil.getConnection();
String sql="SELECT * FROM myuser WHERE username=? AND password=?";
System.out.println(sql);
try {
PreparedStatement pst=conn.prepareStatement(sql);
//为占位符设置具体内容
pst.setString(1, name);
pst.setString(2, password);
//发送sql语句
ResultSet rst=pst.executeQuery();
if(rst.next()) {
System.out.println("登陆成功!");
}else {
System.out.println("用户密码不正确!");
}
}catch(SQLException e) {
e.printStackTrace();
}
return false;
}
/**
* 增加雇员的方法,通过vo类增加信息
* @param emp
* @return
* @throws SQLException
*/
public static boolean insertEmp(Emp emp) throws SQLException{
//取得Connection连接对象
Connection conn=ConnectionUtil.getConnection();
//定义出sql语句
String sql="INSERT INTO emp(ename,job,sal,hiredate,mgr,comm,deptno)"+" VALUES(?,?,?,?,?,?,?)";
//取得发送sql语句对象
PreparedStatement pst=conn.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);
//为占位符赋值
pst.setObject(1,emp.getEname());
pst.setObject(2, emp.getJob());
pst.setObject(3, emp.getSal());
pst.setObject(4, emp.getHiredate());
pst.setObject(5, emp.getMgr());
pst.setObject(6, emp.getComm());
pst.setObject(7, emp.getDeptno());
//执行sql语句
int row=pst.executeUpdate();
//取得自动增长的主键值
ResultSet rst=pst.getGeneratedKeys();
if(rst.next()) {
System.out.println(rst.getObject(1));
}
//关闭连接对象
ConnectionUtil.close(conn);
return row>0;
}
/**
* 通过vo类保存对象实现模糊分页查询
* @param kw 模糊查询的关键字
* @param cp 当前页
* @param ls 每页显示数据
* @return
* @throws Exception
*/
public static List<Emp> selectSplitAll1(String kw,Integer cp,Integer ls) throws Exception{
//创建List集合保存Emp对象
List<Emp> listEmps=new ArrayList<Emp>();
if(kw==null) {
kw="";
}
kw="%"+kw+"%";
//取得连接对象
Connection conn=ConnectionUtil.getConnection();
//准备出sql语句
String sql="SELECT empno,ename,job,hiredate,sal,comm,mgr,deptno FROM emp"+" WhERE ename LIKE ? LIMIT ?,?";
//取得预编译对象
PreparedStatement pst=conn.prepareStatement(sql);
//为占位符设置内容
pst.setObject(1, kw);
pst.setObject(2, (cp-1)*ls);
pst.setObject(3, ls);
//执行sql语句
ResultSet rst=pst.executeQuery();
while(rst.next()) {
listEmps.add(new Emp(rst.getInt("empno"),rst.getString("ename"),
rst.getString("job"),rst.getInt("mgr"),rst.getDate("hiredate"),
rst.getDouble("sal"),rst.getDouble("comm"),rst.getInt("deptno")));
}
//关闭连接
rst.close();
pst.close();
ConnectionUtil.close(conn);
return listEmps;
}
}
工具类代码
package sun.util;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
public class ConnectionUtil {
private static String URL = “jdbc:mysql://localhost:3306/demo?useSSL=true&useUnicode=true&characterEncoding=UTF-8”;
// 连接驱动的全名称
private static String DRIVER = “com.mysql.jdbc.Driver”;
// 用户名
private static String USER = “root”;
// 密码
private static String PASSWORD = “1234”;
// 加载驱动信息
static {
try {
Class.forName(DRIVER);
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
public static Connection getConnection() {
try{
return DriverManager.getConnection(URL,USER,PASSWORD);
}catch(Exception e) {
e.printStackTrace();
}
return null;
}
public static void close(Connection conn) {
if(conn!=null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
VO类代码
public class Emp implements Serializable {
private Integer empno;
private String ename;
private String job;
private Integer mgr;
private Date hiredate;
private Double sal;
private Double comm;
private Integer deptno;
setter以及getter,构造方法省略
参考学习地址
http://blog.mbzvip.tech/