springMVC入门实例(员工系统-Mysql)代码简单易懂

package com.jdbc; 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.List; import com.springemployee.crud.entities.Employee; public class JDBC { PreparedStatement ps; ResultSet rs; //写一个连接数据库的方法 private static Connection getConnection(){ String url="jdbc:mysql://localhost:3306/emp"; String userName="root"; String password="123456"; Connection conn=null; try{ Class.forName("com.mysql.jdbc.Driver"); }catch(ClassNotFoundException e){ System.out.println("找不到驱动！"); e.printStackTrace(); } try{ conn=DriverManager.getConnection(url,userName,password); if(conn!=null){ System.out.println("connection successful"); } }catch(SQLException e){ System.out.println("connection fail"); e.printStackTrace(); } return conn; } //写一个查询数据库语句的方法 public static int insert(Employee employee) { Connection conn = getConnection(); int i = 0; String sql = "insert into Employee (lastName,email,gender,department) values(?,?,?,?)"; PreparedStatement pstmt; try { pstmt = (PreparedStatement) conn.prepareStatement(sql); pstmt.setString(1, employee.getLastName()); pstmt.setString(2, employee.getEmail()); pstmt.setInt(3, employee.getGender()); pstmt.setInt(4, employee.getDepartment()); pstmt.execute(); pstmt.close(); conn.close(); } catch (SQLException e) { e.printStackTrace(); } return i; } public static int update(Employee employee) { Connection conn = getConnection(); int i = 0; String sql = "update Employee set lastName='"+employee.getLastName()+"', email='"+employee.getEmail()+"' ,gender="+employee.getGender()+" ,department="+employee.getDepartment()+" where id=" +employee.getId(); PreparedStatement pstmt; try { pstmt = (PreparedStatement) conn.prepareStatement(sql); i = pstmt.executeUpdate(); System.out.println("resutl: " + i); pstmt.close(); conn.close(); } catch (SQLException e) { e.printStackTrace(); } return i; } public static List<Employee> getAll() { Connection conn = getConnection(); List<Employee> list=new ArrayList<Employee>(); String sql = "select * from Employee"; PreparedStatement pstmt; try { pstmt = (PreparedStatement)conn.prepareStatement(sql); ResultSet rs = pstmt.executeQuery(); // int col = rs.getMetaData().getColumnCount(); System.out.println("============================"); while (rs.next()) { Employee emp=new Employee(); emp.setId(rs.getInt("id")); emp.setLastName(rs.getString("lastName")); emp.setEmail(rs.getString("email")); emp.setGender(rs.getInt("gender")); emp.setDepartment(rs.getInt("department")); list.add(emp); } System.out.println("============================"); } catch (SQLException e) { e.printStackTrace(); } return list; } public static Employee getEmployee(int id) { Connection conn = getConnection(); Employee emp=new Employee(); String sql = "select * from Employee where id="+id; PreparedStatement pstmt; try { pstmt = (PreparedStatement)conn.prepareStatement(sql); ResultSet rs = pstmt.executeQuery(); // int col = rs.getMetaData().getColumnCount(); System.out.println("============================"); while (rs.next()) { emp.setId(rs.getInt("id")); emp.setLastName(rs.getString("lastName")); emp.setEmail(rs.getString("email")); emp.setGender(rs.getInt("gender")); emp.setDepartment(rs.getInt("department")); } System.out.println("============================"); } catch (SQLException e) { e.printStackTrace(); } return emp; } public static int delete(int id) { Connection conn = getConnection(); int i = 0; String sql = "delete from Employee where id="+id; PreparedStatement pstmt; try { pstmt = (PreparedStatement) conn.prepareStatement(sql); i = pstmt.executeUpdate(sql); System.out.println("resutl: " + i); pstmt.close(); conn.close(); } catch (SQLException e) { e.printStackTrace(); } return i; } }