Java JDBC增删改查(CRUD)

最新推荐文章于 2024-03-22 19:39:22 发布

phone13144830339

最新推荐文章于 2024-03-22 19:39:22 发布

阅读量362

点赞数

本文链接：https://blog.csdn.net/phone13144830339/article/details/79122248

版权

MySQL.sql

USE `test`;
DROP TABLE IF EXISTS `emp`;
CREATE TABLE `emp` (
  `EMPNO` int(11) NOT NULL AUTO_INCREMENT,
  `ENAME` varchar(30) DEFAULT NULL,
  `JOB` varchar(30) DEFAULT NULL,
  `MGR` int(11) DEFAULT NULL,
  `HIREDATE` varchar(30) DEFAULT NULL,
  `SAL` int(11) DEFAULT NULL,
  `DEPTNO` int(11) DEFAULT NULL,
  `COMM` int(11) DEFAULT NULL,
  PRIMARY KEY (`EMPNO`)
) ENGINE=InnoDB AUTO_INCREMENT=7935 DEFAULT CHARSET=utf8;
INSERT INTO EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)  
VALUES (7369, 'SMITH', 'CLERK', 7902,'1980-12-17', 'YYYY-MM-DD', 800, null, 20);  
INSERT INTO EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)  
VALUES (7499, 'ALLEN', 'SALESMAN', 7698,'1981-02-20', 'YYYY-MM-DD', 1600, 300, 30);  
INSERT INTO EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)  
VALUES (7521, 'WARD', 'SALESMAN', 7698,'1981-02-22', 'YYYY-MM-DD', 1250, 500, 30);  
INSERT INTO EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)  
VALUES (7566, 'JONES', 'MANAGER', 7839,'1981-04-02', 'YYYY-MM-DD', 2975, null, 20);  
INSERT INTO EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)  
VALUES (7654, 'MARTIN', 'SALESMAN', 7698,'1981-09-28', 'YYYY-MM-DD', 1250, 1400, 30);  
INSERT INTO EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)  
VALUES (7698, 'BLAKE', 'MANAGER', 7839,'1981-05-01', 'YYYY-MM-DD', 2850, null, 30);  
INSERT INTO EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)  
VALUES (7782, 'CLARK', 'MANAGER', 7839,'1981-06-09', 'YYYY-MM-DD', 2450, null, 10);  
INSERT INTO EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)  
VALUES (7788, 'SCOTT', 'ANALYST', 7566,'1987-04-19', 'YYYY-MM-DD', 3000, null, 20);  
INSERT INTO EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)  
VALUES (7839, 'KING', 'PRESIDENT', null,'1981-11-17', 'YYYY-MM-DD', 5000, null, 10);  
INSERT INTO EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)  
VALUES (7844, 'TURNER', 'SALESMAN', 7698,'1981-09-08', 'YYYY-MM-DD', 1500, 0, 30);  
INSERT INTO EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)  
VALUES (7876, 'ADAMS', 'CLERK', 7788,'1987-05-23', 'YYYY-MM-DD', 1100, null, 20);  
INSERT INTO EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)  
VALUES (7900, 'JAMES', 'CLERK', 7698,'1981-12-03', 'YYYY-MM-DD', 950, null, 30);  
INSERT INTO EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)  
VALUES (7902, 'FORD', 'ANALYST', 7566,'1981-12-02', 'YYYY-MM-DD', 3000, null, 20);  
INSERT INTO EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)  
VALUES (7934, 'MILLER', 'CLERK', 7782,'1982-01-23', 'YYYY-MM-DD', 1300, null, 10);

jdbc.properties

#Oracle
#url=jdbc:oracle:thin:@localhost:1521:orcl
#driverClass=oracle.jdbc.OracleDriver
#username=scott
#password=tiger
#MySQL
url=jdbc:mysql://localhost:3306/test
driverClass=com.mysql.jdbc.Driver
username=root
password=123456

package cn.et.utils;

import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Properties;

public class JdbcUtil {
	private JdbcUtil() {}
	static Properties p=new Properties();
	static{
		InputStream is=JdbcUtil.class.getResourceAsStream("/jdbc.properties");
		try {
			p.load(is);
		} catch (IOException e) {
			e.printStackTrace();
		}
	}
	public static Connection getConnection() throws SQLException, ClassNotFoundException {
		String driver = p.getProperty("driverClass");
	    String url = p.getProperty("url");
	    String username = p.getProperty("username");
	    String password = p.getProperty("password");
	    /*Class.forName("com.mysql.jdbc.Driver");
	    Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test","root","123456");*/
	    Class.forName(driver);
	    Connection conn = DriverManager.getConnection(url,username,password);
	    return conn;
	}
}

package cn.et.entity;

public class Employee {
	//EMPNO`, `ENAME`, `JOB`, `MGR`, `HIREDATE`, `SAL`, `DEPTNO`
	private String empno;
	private String ename;
	private String job;
	private String mgr;
	private String hiredate;
	private String sal;
	private String deptno;
	private String comm;
	public String getEmpno() {
		return empno;
	}
	public void setEmpno(String empno) {
		this.empno = empno;
	}
	public String getEname() {
		return ename;
	}
	public void setEname(String ename) {
		this.ename = ename;
	}
	public String getJob() {
		return job;
	}
	public void setJob(String job) {
		this.job = job;
	}
	public String getMgr() {
		return mgr;
	}
	public void setMgr(String mgr) {
		this.mgr = mgr;
	}
	public String getHiredate() {
		return hiredate;
	}
	public void setHiredate(String hiredate) {
		this.hiredate = hiredate;
	}
	public String getSal() {
		return sal;
	}
	public void setSal(String sal) {
		this.sal = sal;
	}
	public String getDeptno() {
		return deptno;
	}
	public void setDeptno(String deptno) {
		this.deptno = deptno;
	}
	public String getComm() {
		return comm;
	}
	public void setComm(String comm) {
		this.comm = comm;
	}
	@Override
	public int hashCode() {
		final int prime = 31;
		int result = 1;
		result = prime * result + ((comm == null) ? 0 : comm.hashCode());
		result = prime * result + ((deptno == null) ? 0 : deptno.hashCode());
		result = prime * result + ((empno == null) ? 0 : empno.hashCode());
		result = prime * result + ((ename == null) ? 0 : ename.hashCode());
		result = prime * result + ((hiredate == null) ? 0 : hiredate.hashCode());
		result = prime * result + ((job == null) ? 0 : job.hashCode());
		result = prime * result + ((mgr == null) ? 0 : mgr.hashCode());
		result = prime * result + ((sal == null) ? 0 : sal.hashCode());
		return result;
	}
	@Override
	public boolean equals(Object obj) {
		if (this == obj)
			return true;
		if (obj == null)
			return false;
		if (getClass() != obj.getClass())
			return false;
		Employee other = (Employee) obj;
		if (comm == null) {
			if (other.comm != null)
				return false;
		} else if (!comm.equals(other.comm))
			return false;
		if (deptno == null) {
			if (other.deptno != null)
				return false;
		} else if (!deptno.equals(other.deptno))
			return false;
		if (empno == null) {
			if (other.empno != null)
				return false;
		} else if (!empno.equals(other.empno))
			return false;
		if (ename == null) {
			if (other.ename != null)
				return false;
		} else if (!ename.equals(other.ename))
			return false;
		if (hiredate == null) {
			if (other.hiredate != null)
				return false;
		} else if (!hiredate.equals(other.hiredate))
			return false;
		if (job == null) {
			if (other.job != null)
				return false;
		} else if (!job.equals(other.job))
			return false;
		if (mgr == null) {
			if (other.mgr != null)
				return false;
		} else if (!mgr.equals(other.mgr))
			return false;
		if (sal == null) {
			if (other.sal != null)
				return false;
		} else if (!sal.equals(other.sal))
			return false;
		return true;
	}
	@Override
	public String toString() {
		return "Employee [empno=" + empno + ", ename=" + ename + ", job=" + job + ", mgr=" + mgr + ", hiredate="
				+ hiredate + ", sal=" + sal + ", deptno=" + deptno + ", comm=" + comm + "]";
	}
}

package cn.et.jdbc;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import cn.et.entity.Employee;
import cn.et.utils.JdbcUtil;

public class Jdbc {
	public int insert(Employee emp) throws Exception {
	    Connection conn = JdbcUtil.getConnection();
	    int i = 0;
	    //INSERT INTO EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) VALUES (7566, 'JONES', 'MANAGER', 7839,'1981-04-02', 'YYYY-MM-DD', 2975, null, 20);
	    String sql = "INSERT INTO EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) VALUES (?, ?, ?, ?, ?, ?, ?, ?)";
	    PreparedStatement pstmt;
	    try {
	        pstmt = (PreparedStatement) conn.prepareStatement(sql);
	        pstmt.setString(1, emp.getEmpno());
	        pstmt.setString(2, emp.getEname());
	        pstmt.setString(3, emp.getJob());
	        pstmt.setString(4, emp.getMgr());
	        pstmt.setString(5,emp.getHiredate());
	        pstmt.setString(6, emp.getSal());
	        pstmt.setString(7, emp.getComm());
	        pstmt.setString(8,emp.getDeptno());
	        i = pstmt.executeUpdate();
	        pstmt.close();
	        conn.close();
	    } catch (SQLException e) {
	    	conn.rollback();
	        e.printStackTrace();
	    }
	    return i;
	}
	
	//update
	public int updateById(Employee emp) throws Exception {
	    Connection conn = JdbcUtil.getConnection();
	    int i = 0;
	    String sql = "update emp set ename='" + emp.getEname() + "' where empno='" + emp.getEmpno() + "'";
	    PreparedStatement pstmt;
	    try {
	        pstmt = (PreparedStatement) conn.prepareStatement(sql);
	        i = pstmt.executeUpdate();
	        System.out.println("resutl: " + i);
	        pstmt.close();
	        conn.close();
	    } catch (SQLException e) {
	    	conn.rollback();
	        e.printStackTrace();
	    }
	    return i;
	}
	
	//selete
	public Integer seleteAll() throws Exception {
	    Connection conn = JdbcUtil.getConnection();
	    conn.setAutoCommit(true);
	    conn.commit();
	    String sql = "select * from emp";
	    PreparedStatement pstmt;
	    try {
	        pstmt = (PreparedStatement)conn.prepareStatement(sql);
	        ResultSet rs = pstmt.executeQuery();
	        int col = rs.getMetaData().getColumnCount();
	   
	        while (rs.next()) {
	            for (int i = 1; i <= col; i++) {
	                System.out.print(rs.getString(i) + "\t");
	                if (((i == 2)) && (rs.getString(i).length() < 10)) {
	                    System.out.print("\t");
	                }
	             }
	            System.out.println("");
	        }
	     
	    } catch (SQLException e) {
	    	conn.rollback();
	        e.printStackTrace();
	    }
	    return null;
	}
	package cn.et;

import org.junit.Test;

import cn.et.entity.Employee;
import cn.et.jdbc.Jdbc;

public class Checkout {
	
	static Jdbc j = new Jdbc();
	@Test
	public void insertTesting() throws Exception {	
		Employee emp = new Employee();
		emp.setDeptno("20");
		emp.setComm("1000");
		emp.setEmpno("7878");
		emp.setHiredate("2018-2-30");
		emp.setJob("MANAGER");
		emp.setSal("12000");
		emp.setEname("LUCHENG");
		emp.setMgr("7566");
		j.insert(emp);
	}
	
	@Test
	public void seleteTesting() throws Exception {
		j.seleteAll();
	}
	@Test
	public void updateTesting() throws Exception {
		Employee emp = new Employee();
		emp.setEmpno("7878");
		emp.setEname("LU");
		j.updateById(emp);
	}
	@Test
	public void deleteTesting() throws Exception {
		Integer empno = 7878;
		j.deleteByEmpno(empno);
	}
}


//deletepublic int deleteByEmpno(Integer empno) throws Exception { Connection conn = JdbcUtil.getConnection(); int i = 0; String sql = "delete from EMP where EMPNO='" + empno + "'"; PreparedStatement pstmt; try { pstmt = (PreparedStatement) conn.prepareStatement(sql);
 i = pstmt.executeUpdate(); System.out.println("resutl: " + i); pstmt.close(); conn.close(); } catch (SQLException e) { conn.rollback(); e.printStackTrace(); } return i;}}

phone13144830339

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
Java JDBC增删改查(CRUD)

MySQL.sqlUSE `test`;DROP TABLE IF EXISTS `emp`;CREATE TABLE `emp` ( `EMPNO` int(11) NOT NULL AUTO_INCREMENT, `ENAME` varchar(30) DEFAULT NULL, `JOB` varchar(30) DEFAULT NULL, `MGR` int(11)
复制链接

扫一扫