444444

1使用IDEA创建maven工程

1.1打开idea，在首页选择Create New Project

1.2选择创建maven项目，设置SDK、勾选Create from archetype，点击next下一步

1.3设置项目路径、项目名称、包名等

1.4点击file->settings，设置maven home路径

1.5点击file->settings，设置Java Compiler版本

1.6点击file->Project Structures，设置jdk版本

回到顶部

2引入mysql依赖包

2.1在pom.xml中添加mysql依赖

<dependencies>
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>5.1.47</version>
        <scope>runtime</scope>
    </dependency>
</dependencies>

2.2在maven加载包

点击maven左上角的Reimport All Maven Projects 加载pox.xml中的jar包，之后Dependencies中会出现依赖的jar包

回到顶部

3准备数据

CREATE TABLE `employee_table` (
  `employee_id` int(11) NOT NULL auto_increment,
  `employee_name` varchar(60) NOT NULL,
  `email` varchar(45) NOT NULL,
  `salary` double default NULL,
  `date_of_joining` datetime default NULL,
  `bonus` decimal(10,0) default NULL,
  PRIMARY KEY  (`employee_id`)
) ;

回到顶部

4编写代码

4.1创建如下包名

com.demos、com.demos.util、com.demos.model、com.demos.dao、com.demos.dao.impl

4.2创建DBUtil工具类

package com.demos.util;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

public class DBUtil {
    private static final String DB_DRIVER_CLASS="com.mysql.jdbc.Driver";
    private static final String DB_USERNAME="root";
    private static final String DB_PASSWORD="123456";
    private static final String DB_URL ="jdbc:mysql://localhost:3306/demos";

    private static Connection connection = null;
    static{
        try {
            // 加载数据库驱动
            Class.forName(DB_DRIVER_CLASS);
            // 获取数据库连接
            connection = DriverManager.getConnection(DB_URL, DB_USERNAME, DB_PASSWORD);
        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        }
    }

    public static Connection getConnection(){
        return connection;
    }
}

4.3创建Employee实体类

package com.demos.model;

import java.math.BigDecimal;
import java.util.Date;

public class Employee {

    private int employee_id;
    private String employeeName;
    private String email;
    private Double salary;
    private Date doj;
    private BigDecimal bonus;

    public int getEmployee_id() {
        return employee_id;
    }
    public void setEmployee_id(int employee_id) {
        this.employee_id = employee_id;
    }
    public String getEmployeeName() {
        return employeeName;
    }
    public void setEmployeeName(String employeeName) {
        this.employeeName = employeeName;
    }
    public String getEmail() {
        return email;
    }
    public void setEmail(String email) {
        this.email = email;
    }
    public Double getSalary() {
        return salary;
    }
    public void setSalary(Double salary) {
        this.salary = salary;
    }
    public Date getDoj() {
        return doj;
    }
    public void setDoj(Date doj) {
        this.doj = doj;
    }
    public BigDecimal getBonus() {
        return bonus;
    }
    public void setBonus(BigDecimal bonus) {
        this.bonus = bonus;
    }
    @Override
    public String toString() {
        return "Employee [employee_id=" + employee_id + ", employeeName=" + employeeName + ", email=" + email
                + ", salary=" + salary + ", doj=" + doj + ", bonus=" + bonus + "]";
    }
}

4.4创建EmployeeDAO接口

package com.demos.dao;

import java.util.List;
import com.demos.model.Employee;
public interface EmployeeDAO {

    public abstract void createEmployee(Employee employee);
    public abstract Employee getEmployeeById(Integer employeeId);
    public abstract void updateEmployeeEmailById(String newEmail,Integer employeeId);
    public abstract void deleteEmployeeById(Integer employeeId);
    public abstract List<Employee> getAllEmployeesInfo();
}

4.5创建EmployeeDAOImpl实现EmployeeDAO接口

package com.demos.dao.impl;

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import com.demos.dao.EmployeeDAO;
import com.demos.model.Employee;
import com.demos.util.DBUtil;
import com.demos.dao.EmployeeDAO;
import com.demos.model.Employee;
import com.demos.util.DBUtil;

public class EmployeeDAOImpl implements EmployeeDAO {

    @Override
    public void createEmployee(Employee employee) {

        String SQL = "INSERT INTO employee_table(employee_name,email,salary,date_of_joining,bonus)VALUES(?,?,?,?,?)";
        try(Connection connection = DBUtil.getConnection(); PreparedStatement ps = connection.prepareStatement(SQL)) {

            ps.setString(1, employee.getEmployeeName());
            ps.setString(2, employee.getEmail());
            ps.setDouble(3, employee.getSalary());
            ps.setDate(4, new Date(employee.getDoj().getTime()));
            ps.setBigDecimal(5, employee.getBonus());

            int executeUpdate = ps.executeUpdate();

            if(executeUpdate ==1){
                System.out.println("Employee is crated..");
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    @Override
    public Employee getEmployeeById(Integer employeeId) {
        Employee employee = null;
        String SQL = "SELECT *FROM employee_table WHERE employee_id=?";
        try(Connection connection = DBUtil.getConnection();PreparedStatement ps = connection.prepareStatement(SQL)) {

            ps.setInt(1, employeeId);

            ResultSet rs = ps.executeQuery();
            if (rs.next()) {
                int empId = rs.getInt("employee_id");
                String eName = rs.getString("employee_name");
                String email = rs.getString("email");
                Double salary = rs.getDouble("salary");
                BigDecimal bonus = rs.getBigDecimal("bonus");
                Date date = rs.getDate("date_of_joining");

                employee = new Employee();
                employee.setEmployeeName(eName);
                employee.setBonus(bonus);
                employee.setDoj(date);
                employee.setEmail(email);
                employee.setEmployee_id(empId);
                employee.setSalary(salary);
            }

        } catch (Exception e) {
            e.printStackTrace();
        }

        return employee;
    }

    @Override
    public void updateEmployeeEmailById(String newEmail, Integer employeeId) {

        String SQL = "UPDATE employee_table set email=? WHERE employee_id=?;";
        try(Connection connection = DBUtil.getConnection();PreparedStatement ps = connection.prepareStatement(SQL)) {

            ps.setString(1, newEmail);
            ps.setInt(2,employeeId);

            int executeUpdate = ps.executeUpdate();

            if(executeUpdate ==1){
                System.out.println("Employee email is updated..");
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    @Override
    public void deleteEmployeeById(Integer employeeId) {

        String SQL = "DELETE FROM employee_table WHERE employee_id=?;";
        try(Connection connection = DBUtil.getConnection();PreparedStatement ps = connection.prepareStatement(SQL)) {

            ps.setInt(1,employeeId);

            int executeUpdate = ps.executeUpdate();

            if(executeUpdate ==1){
                System.out.println("Employee is deleted with ID::"+employeeId);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }

    }

    @Override
    public List<Employee> getAllEmployeesInfo() {

        List<Employee> empList = new ArrayList<>();
        String SQL = "SELECT *FROM employee_table";
        try(Connection connection = DBUtil.getConnection();PreparedStatement ps = connection.prepareStatement(SQL)) {

            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                int empId = rs.getInt("employee_id");
                String eName = rs.getString("employee_name");
                String email = rs.getString("email");
                Double salary = rs.getDouble("salary");
                BigDecimal bonus = rs.getBigDecimal("bonus");
                Date date = rs.getDate("date_of_joining");

                Employee employee = new Employee();
                employee.setEmployeeName(eName);
                employee.setBonus(bonus);
                employee.setDoj(date);
                employee.setEmail(email);
                employee.setEmployee_id(empId);
                employee.setSalary(salary);

                empList.add(employee);
            }

        } catch (Exception e) {
            e.printStackTrace();
        }
        return empList;
    }
}

4.6创建JDBCTest测试上述代码

package com.demos;

import java.math.BigDecimal;
import java.util.Date;
import java.util.List;

import com.demos.dao.EmployeeDAO;
import com.demos.dao.impl.EmployeeDAOImpl;
import com.demos.model.Employee;

public class JDBCTest {

    public static void main(String[] args) {
        try {
            EmployeeDAO employeeDAO = new EmployeeDAOImpl();
            Employee employee = getEmployee();
            employeeDAO.createEmployee(employee);
            //getEmployeeById(employeeDAO);
            //employeeDAO.updateEmployeeEmailById("sam.cs2014@gmail.com", 4);
            //employeeDAO.deleteEmployeeById(6);
            //getAllEmployeesInfo(employeeDAO);
        }
        catch (Exception ex) {
            String msg=ex.toString();
            String error=ex.toString();
        }
    }

    private static void getAllEmployeesInfo(EmployeeDAO employeeDAO) {
        List<Employee> empList = employeeDAO.getAllEmployeesInfo();
        for (Employee employee : empList) {
            System.out.println(employee);
        }
    }

    private static void getEmployeeById(EmployeeDAO employeeDAO) {
        Employee employee2 = employeeDAO.getEmployeeById(4);
        if (employee2 != null) {
            System.out.println(employee2);
        } else {
            System.out.println("Employee does not exist..");
        }
    }

    private static Employee getEmployee() {
        Employee employee = new Employee();
        employee.setBonus(new BigDecimal(290));
        employee.setDoj(new Date());
        employee.setEmployeeName("John");
        employee.setEmail("jonh.cs2016@yahoo.com");
        employee.setSalary(90000.00);
        return employee;
    }
}

回到顶部

5测试程序

在JDBCTest代码块中右击，选择Debug 'JDBCTest.main()'，开始调试程序

1使用IDEA创建maven工程

1.1打开idea，在首页选择Create New Project

1.2选择创建maven项目，设置SDK、勾选Create from archetype，点击next下一步

1.3设置项目路径、项目名称、包名等

1.4点击file->settings，设置maven home路径

1.5点击file->settings，设置Java Compiler版本

1.6点击file->Project Structures，设置jdk版本

回到顶部

2引入mysql依赖包

2.1在pom.xml中添加mysql依赖

<dependencies>
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>5.1.47</version>
        <scope>runtime</scope>
    </dependency>
</dependencies>

2.2在maven加载包

点击maven左上角的Reimport All Maven Projects 加载pox.xml中的jar包，之后Dependencies中会出现依赖的jar包

回到顶部

3准备数据

CREATE TABLE `employee_table` (
  `employee_id` int(11) NOT NULL auto_increment,
  `employee_name` varchar(60) NOT NULL,
  `email` varchar(45) NOT NULL,
  `salary` double default NULL,
  `date_of_joining` datetime default NULL,
  `bonus` decimal(10,0) default NULL,
  PRIMARY KEY  (`employee_id`)
) ;

回到顶部

4编写代码

4.1创建如下包名

com.demos、com.demos.util、com.demos.model、com.demos.dao、com.demos.dao.impl

4.2创建DBUtil工具类

package com.demos.util;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

public class DBUtil {
    private static final String DB_DRIVER_CLASS="com.mysql.jdbc.Driver";
    private static final String DB_USERNAME="root";
    private static final String DB_PASSWORD="123456";
    private static final String DB_URL ="jdbc:mysql://localhost:3306/demos";

    private static Connection connection = null;
    static{
        try {
            // 加载数据库驱动
            Class.forName(DB_DRIVER_CLASS);
            // 获取数据库连接
            connection = DriverManager.getConnection(DB_URL, DB_USERNAME, DB_PASSWORD);
        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        }
    }

    public static Connection getConnection(){
        return connection;
    }
}

4.3创建Employee实体类

package com.demos.model;

import java.math.BigDecimal;
import java.util.Date;

public class Employee {

    private int employee_id;
    private String employeeName;
    private String email;
    private Double salary;
    private Date doj;
    private BigDecimal bonus;

    public int getEmployee_id() {
        return employee_id;
    }
    public void setEmployee_id(int employee_id) {
        this.employee_id = employee_id;
    }
    public String getEmployeeName() {
        return employeeName;
    }
    public void setEmployeeName(String employeeName) {
        this.employeeName = employeeName;
    }
    public String getEmail() {
        return email;
    }
    public void setEmail(String email) {
        this.email = email;
    }
    public Double getSalary() {
        return salary;
    }
    public void setSalary(Double salary) {
        this.salary = salary;
    }
    public Date getDoj() {
        return doj;
    }
    public void setDoj(Date doj) {
        this.doj = doj;
    }
    public BigDecimal getBonus() {
        return bonus;
    }
    public void setBonus(BigDecimal bonus) {
        this.bonus = bonus;
    }
    @Override
    public String toString() {
        return "Employee [employee_id=" + employee_id + ", employeeName=" + employeeName + ", email=" + email
                + ", salary=" + salary + ", doj=" + doj + ", bonus=" + bonus + "]";
    }
}

4.4创建EmployeeDAO接口

package com.demos.dao;

import java.util.List;
import com.demos.model.Employee;
public interface EmployeeDAO {

    public abstract void createEmployee(Employee employee);
    public abstract Employee getEmployeeById(Integer employeeId);
    public abstract void updateEmployeeEmailById(String newEmail,Integer employeeId);
    public abstract void deleteEmployeeById(Integer employeeId);
    public abstract List<Employee> getAllEmployeesInfo();
}

4.5创建EmployeeDAOImpl实现EmployeeDAO接口

package com.demos.dao.impl;

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import com.demos.dao.EmployeeDAO;
import com.demos.model.Employee;
import com.demos.util.DBUtil;
import com.demos.dao.EmployeeDAO;
import com.demos.model.Employee;
import com.demos.util.DBUtil;

public class EmployeeDAOImpl implements EmployeeDAO {

    @Override
    public void createEmployee(Employee employee) {

        String SQL = "INSERT INTO employee_table(employee_name,email,salary,date_of_joining,bonus)VALUES(?,?,?,?,?)";
        try(Connection connection = DBUtil.getConnection(); PreparedStatement ps = connection.prepareStatement(SQL)) {

            ps.setString(1, employee.getEmployeeName());
            ps.setString(2, employee.getEmail());
            ps.setDouble(3, employee.getSalary());
            ps.setDate(4, new Date(employee.getDoj().getTime()));
            ps.setBigDecimal(5, employee.getBonus());

            int executeUpdate = ps.executeUpdate();

            if(executeUpdate ==1){
                System.out.println("Employee is crated..");
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    @Override
    public Employee getEmployeeById(Integer employeeId) {
        Employee employee = null;
        String SQL = "SELECT *FROM employee_table WHERE employee_id=?";
        try(Connection connection = DBUtil.getConnection();PreparedStatement ps = connection.prepareStatement(SQL)) {

            ps.setInt(1, employeeId);

            ResultSet rs = ps.executeQuery();
            if (rs.next()) {
                int empId = rs.getInt("employee_id");
                String eName = rs.getString("employee_name");
                String email = rs.getString("email");
                Double salary = rs.getDouble("salary");
                BigDecimal bonus = rs.getBigDecimal("bonus");
                Date date = rs.getDate("date_of_joining");

                employee = new Employee();
                employee.setEmployeeName(eName);
                employee.setBonus(bonus);
                employee.setDoj(date);
                employee.setEmail(email);
                employee.setEmployee_id(empId);
                employee.setSalary(salary);
            }

        } catch (Exception e) {
            e.printStackTrace();
        }

        return employee;
    }

    @Override
    public void updateEmployeeEmailById(String newEmail, Integer employeeId) {

        String SQL = "UPDATE employee_table set email=? WHERE employee_id=?;";
        try(Connection connection = DBUtil.getConnection();PreparedStatement ps = connection.prepareStatement(SQL)) {

            ps.setString(1, newEmail);
            ps.setInt(2,employeeId);

            int executeUpdate = ps.executeUpdate();

            if(executeUpdate ==1){
                System.out.println("Employee email is updated..");
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    @Override
    public void deleteEmployeeById(Integer employeeId) {

        String SQL = "DELETE FROM employee_table WHERE employee_id=?;";
        try(Connection connection = DBUtil.getConnection();PreparedStatement ps = connection.prepareStatement(SQL)) {

            ps.setInt(1,employeeId);

            int executeUpdate = ps.executeUpdate();

            if(executeUpdate ==1){
                System.out.println("Employee is deleted with ID::"+employeeId);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }

    }

    @Override
    public List<Employee> getAllEmployeesInfo() {

        List<Employee> empList = new ArrayList<>();
        String SQL = "SELECT *FROM employee_table";
        try(Connection connection = DBUtil.getConnection();PreparedStatement ps = connection.prepareStatement(SQL)) {

            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                int empId = rs.getInt("employee_id");
                String eName = rs.getString("employee_name");
                String email = rs.getString("email");
                Double salary = rs.getDouble("salary");
                BigDecimal bonus = rs.getBigDecimal("bonus");
                Date date = rs.getDate("date_of_joining");

                Employee employee = new Employee();
                employee.setEmployeeName(eName);
                employee.setBonus(bonus);
                employee.setDoj(date);
                employee.setEmail(email);
                employee.setEmployee_id(empId);
                employee.setSalary(salary);

                empList.add(employee);
            }

        } catch (Exception e) {
            e.printStackTrace();
        }
        return empList;
    }
}

4.6创建JDBCTest测试上述代码

package com.demos;

import java.math.BigDecimal;
import java.util.Date;
import java.util.List;

import com.demos.dao.EmployeeDAO;
import com.demos.dao.impl.EmployeeDAOImpl;
import com.demos.model.Employee;

public class JDBCTest {

    public static void main(String[] args) {
        try {
            EmployeeDAO employeeDAO = new EmployeeDAOImpl();
            Employee employee = getEmployee();
            employeeDAO.createEmployee(employee);
            //getEmployeeById(employeeDAO);
            //employeeDAO.updateEmployeeEmailById("sam.cs2014@gmail.com", 4);
            //employeeDAO.deleteEmployeeById(6);
            //getAllEmployeesInfo(employeeDAO);
        }
        catch (Exception ex) {
            String msg=ex.toString();
            String error=ex.toString();
        }
    }

    private static void getAllEmployeesInfo(EmployeeDAO employeeDAO) {
        List<Employee> empList = employeeDAO.getAllEmployeesInfo();
        for (Employee employee : empList) {
            System.out.println(employee);
        }
    }

    private static void getEmployeeById(EmployeeDAO employeeDAO) {
        Employee employee2 = employeeDAO.getEmployeeById(4);
        if (employee2 != null) {
            System.out.println(employee2);
        } else {
            System.out.println("Employee does not exist..");
        }
    }

    private static Employee getEmployee() {
        Employee employee = new Employee();
        employee.setBonus(new BigDecimal(290));
        employee.setDoj(new Date());
        employee.setEmployeeName("John");
        employee.setEmail("jonh.cs2016@yahoo.com");
        employee.setSalary(90000.00);
        return employee;
    }
}

回到顶部

5测试程序

在JDBCTest代码块中右击，选择Debug 'JDBCTest.main()'，开始调试程序

1使用IDEA创建maven工程

1.1打开idea，在首页选择Create New Project

1.2选择创建maven项目，设置SDK、勾选Create from archetype，点击next下一步

1.3设置项目路径、项目名称、包名等

1.4点击file->settings，设置maven home路径

1.5点击file->settings，设置Java Compiler版本

1.6点击file->Project Structures，设置jdk版本

回到顶部

2引入mysql依赖包

2.1在pom.xml中添加mysql依赖

<dependencies>
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>5.1.47</version>
        <scope>runtime</scope>
    </dependency>
</dependencies>

2.2在maven加载包

点击maven左上角的Reimport All Maven Projects 加载pox.xml中的jar包，之后Dependencies中会出现依赖的jar包

回到顶部

3准备数据

CREATE TABLE `employee_table` (
  `employee_id` int(11) NOT NULL auto_increment,
  `employee_name` varchar(60) NOT NULL,
  `email` varchar(45) NOT NULL,
  `salary` double default NULL,
  `date_of_joining` datetime default NULL,
  `bonus` decimal(10,0) default NULL,
  PRIMARY KEY  (`employee_id`)
) ;

回到顶部

4编写代码

4.1创建如下包名

com.demos、com.demos.util、com.demos.model、com.demos.dao、com.demos.dao.impl

4.2创建DBUtil工具类

package com.demos.util;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

public class DBUtil {
    private static final String DB_DRIVER_CLASS="com.mysql.jdbc.Driver";
    private static final String DB_USERNAME="root";
    private static final String DB_PASSWORD="123456";
    private static final String DB_URL ="jdbc:mysql://localhost:3306/demos";

    private static Connection connection = null;
    static{
        try {
            // 加载数据库驱动
            Class.forName(DB_DRIVER_CLASS);
            // 获取数据库连接
            connection = DriverManager.getConnection(DB_URL, DB_USERNAME, DB_PASSWORD);
        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        }
    }

    public static Connection getConnection(){
        return connection;
    }
}

4.3创建Employee实体类

package com.demos.model;

import java.math.BigDecimal;
import java.util.Date;

public class Employee {

    private int employee_id;
    private String employeeName;
    private String email;
    private Double salary;
    private Date doj;
    private BigDecimal bonus;

    public int getEmployee_id() {
        return employee_id;
    }
    public void setEmployee_id(int employee_id) {
        this.employee_id = employee_id;
    }
    public String getEmployeeName() {
        return employeeName;
    }
    public void setEmployeeName(String employeeName) {
        this.employeeName = employeeName;
    }
    public String getEmail() {
        return email;
    }
    public void setEmail(String email) {
        this.email = email;
    }
    public Double getSalary() {
        return salary;
    }
    public void setSalary(Double salary) {
        this.salary = salary;
    }
    public Date getDoj() {
        return doj;
    }
    public void setDoj(Date doj) {
        this.doj = doj;
    }
    public BigDecimal getBonus() {
        return bonus;
    }
    public void setBonus(BigDecimal bonus) {
        this.bonus = bonus;
    }
    @Override
    public String toString() {
        return "Employee [employee_id=" + employee_id + ", employeeName=" + employeeName + ", email=" + email
                + ", salary=" + salary + ", doj=" + doj + ", bonus=" + bonus + "]";
    }
}

4.4创建EmployeeDAO接口

package com.demos.dao;

import java.util.List;
import com.demos.model.Employee;
public interface EmployeeDAO {

    public abstract void createEmployee(Employee employee);
    public abstract Employee getEmployeeById(Integer employeeId);
    public abstract void updateEmployeeEmailById(String newEmail,Integer employeeId);
    public abstract void deleteEmployeeById(Integer employeeId);
    public abstract List<Employee> getAllEmployeesInfo();
}

4.5创建EmployeeDAOImpl实现EmployeeDAO接口

package com.demos.dao.impl;

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import com.demos.dao.EmployeeDAO;
import com.demos.model.Employee;
import com.demos.util.DBUtil;
import com.demos.dao.EmployeeDAO;
import com.demos.model.Employee;
import com.demos.util.DBUtil;

public class EmployeeDAOImpl implements EmployeeDAO {

    @Override
    public void createEmployee(Employee employee) {

        String SQL = "INSERT INTO employee_table(employee_name,email,salary,date_of_joining,bonus)VALUES(?,?,?,?,?)";
        try(Connection connection = DBUtil.getConnection(); PreparedStatement ps = connection.prepareStatement(SQL)) {

            ps.setString(1, employee.getEmployeeName());
            ps.setString(2, employee.getEmail());
            ps.setDouble(3, employee.getSalary());
            ps.setDate(4, new Date(employee.getDoj().getTime()));
            ps.setBigDecimal(5, employee.getBonus());

            int executeUpdate = ps.executeUpdate();

            if(executeUpdate ==1){
                System.out.println("Employee is crated..");
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    @Override
    public Employee getEmployeeById(Integer employeeId) {
        Employee employee = null;
        String SQL = "SELECT *FROM employee_table WHERE employee_id=?";
        try(Connection connection = DBUtil.getConnection();PreparedStatement ps = connection.prepareStatement(SQL)) {

            ps.setInt(1, employeeId);

            ResultSet rs = ps.executeQuery();
            if (rs.next()) {
                int empId = rs.getInt("employee_id");
                String eName = rs.getString("employee_name");
                String email = rs.getString("email");
                Double salary = rs.getDouble("salary");
                BigDecimal bonus = rs.getBigDecimal("bonus");
                Date date = rs.getDate("date_of_joining");

                employee = new Employee();
                employee.setEmployeeName(eName);
                employee.setBonus(bonus);
                employee.setDoj(date);
                employee.setEmail(email);
                employee.setEmployee_id(empId);
                employee.setSalary(salary);
            }

        } catch (Exception e) {
            e.printStackTrace();
        }

        return employee;
    }

    @Override
    public void updateEmployeeEmailById(String newEmail, Integer employeeId) {

        String SQL = "UPDATE employee_table set email=? WHERE employee_id=?;";
        try(Connection connection = DBUtil.getConnection();PreparedStatement ps = connection.prepareStatement(SQL)) {

            ps.setString(1, newEmail);
            ps.setInt(2,employeeId);

            int executeUpdate = ps.executeUpdate();

            if(executeUpdate ==1){
                System.out.println("Employee email is updated..");
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    @Override
    public void deleteEmployeeById(Integer employeeId) {

        String SQL = "DELETE FROM employee_table WHERE employee_id=?;";
        try(Connection connection = DBUtil.getConnection();PreparedStatement ps = connection.prepareStatement(SQL)) {

            ps.setInt(1,employeeId);

            int executeUpdate = ps.executeUpdate();

            if(executeUpdate ==1){
                System.out.println("Employee is deleted with ID::"+employeeId);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }

    }

    @Override
    public List<Employee> getAllEmployeesInfo() {

        List<Employee> empList = new ArrayList<>();
        String SQL = "SELECT *FROM employee_table";
        try(Connection connection = DBUtil.getConnection();PreparedStatement ps = connection.prepareStatement(SQL)) {

            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                int empId = rs.getInt("employee_id");
                String eName = rs.getString("employee_name");
                String email = rs.getString("email");
                Double salary = rs.getDouble("salary");
                BigDecimal bonus = rs.getBigDecimal("bonus");
                Date date = rs.getDate("date_of_joining");

                Employee employee = new Employee();
                employee.setEmployeeName(eName);
                employee.setBonus(bonus);
                employee.setDoj(date);
                employee.setEmail(email);
                employee.setEmployee_id(empId);
                employee.setSalary(salary);

                empList.add(employee);
            }

        } catch (Exception e) {
            e.printStackTrace();
        }
        return empList;
    }
}

4.6创建JDBCTest测试上述代码

package com.demos;

import java.math.BigDecimal;
import java.util.Date;
import java.util.List;

import com.demos.dao.EmployeeDAO;
import com.demos.dao.impl.EmployeeDAOImpl;
import com.demos.model.Employee;

public class JDBCTest {

    public static void main(String[] args) {
        try {
            EmployeeDAO employeeDAO = new EmployeeDAOImpl();
            Employee employee = getEmployee();
            employeeDAO.createEmployee(employee);
            //getEmployeeById(employeeDAO);
            //employeeDAO.updateEmployeeEmailById("sam.cs2014@gmail.com", 4);
            //employeeDAO.deleteEmployeeById(6);
            //getAllEmployeesInfo(employeeDAO);
        }
        catch (Exception ex) {
            String msg=ex.toString();
            String error=ex.toString();
        }
    }

    private static void getAllEmployeesInfo(EmployeeDAO employeeDAO) {
        List<Employee> empList = employeeDAO.getAllEmployeesInfo();
        for (Employee employee : empList) {
            System.out.println(employee);
        }
    }

    private static void getEmployeeById(EmployeeDAO employeeDAO) {
        Employee employee2 = employeeDAO.getEmployeeById(4);
        if (employee2 != null) {
            System.out.println(employee2);
        } else {
            System.out.println("Employee does not exist..");
        }
    }

    private static Employee getEmployee() {
        Employee employee = new Employee();
        employee.setBonus(new BigDecimal(290));
        employee.setDoj(new Date());
        employee.setEmployeeName("John");
        employee.setEmail("jonh.cs2016@yahoo.com");
        employee.setSalary(90000.00);
        return employee;
    }
}

回到顶部

5测试程序

在JDBCTest代码块中右击，选择Debug 'JDBCTest.main()'，开始调试程序

1使用IDEA创建maven工程

1.1打开idea，在首页选择Create New Project

1.2选择创建maven项目，设置SDK、勾选Create from archetype，点击next下一步

1.3设置项目路径、项目名称、包名等

1.4点击file->settings，设置maven home路径

1.5点击file->settings，设置Java Compiler版本

1.6点击file->Project Structures，设置jdk版本

回到顶部

2引入mysql依赖包

2.1在pom.xml中添加mysql依赖

<dependencies>
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>5.1.47</version>
        <scope>runtime</scope>
    </dependency>
</dependencies>

2.2在maven加载包

点击maven左上角的Reimport All Maven Projects 加载pox.xml中的jar包，之后Dependencies中会出现依赖的jar包

回到顶部

3准备数据

CREATE TABLE `employee_table` (
  `employee_id` int(11) NOT NULL auto_increment,
  `employee_name` varchar(60) NOT NULL,
  `email` varchar(45) NOT NULL,
  `salary` double default NULL,
  `date_of_joining` datetime default NULL,
  `bonus` decimal(10,0) default NULL,
  PRIMARY KEY  (`employee_id`)
) ;

回到顶部

4编写代码

4.1创建如下包名

com.demos、com.demos.util、com.demos.model、com.demos.dao、com.demos.dao.impl

4.2创建DBUtil工具类

package com.demos.util;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

public class DBUtil {
    private static final String DB_DRIVER_CLASS="com.mysql.jdbc.Driver";
    private static final String DB_USERNAME="root";
    private static final String DB_PASSWORD="123456";
    private static final String DB_URL ="jdbc:mysql://localhost:3306/demos";

    private static Connection connection = null;
    static{
        try {
            // 加载数据库驱动
            Class.forName(DB_DRIVER_CLASS);
            // 获取数据库连接
            connection = DriverManager.getConnection(DB_URL, DB_USERNAME, DB_PASSWORD);
        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        }
    }

    public static Connection getConnection(){
        return connection;
    }
}

4.3创建Employee实体类

package com.demos.model;

import java.math.BigDecimal;
import java.util.Date;

public class Employee {

    private int employee_id;
    private String employeeName;
    private String email;
    private Double salary;
    private Date doj;
    private BigDecimal bonus;

    public int getEmployee_id() {
        return employee_id;
    }
    public void setEmployee_id(int employee_id) {
        this.employee_id = employee_id;
    }
    public String getEmployeeName() {
        return employeeName;
    }
    public void setEmployeeName(String employeeName) {
        this.employeeName = employeeName;
    }
    public String getEmail() {
        return email;
    }
    public void setEmail(String email) {
        this.email = email;
    }
    public Double getSalary() {
        return salary;
    }
    public void setSalary(Double salary) {
        this.salary = salary;
    }
    public Date getDoj() {
        return doj;
    }
    public void setDoj(Date doj) {
        this.doj = doj;
    }
    public BigDecimal getBonus() {
        return bonus;
    }
    public void setBonus(BigDecimal bonus) {
        this.bonus = bonus;
    }
    @Override
    public String toString() {
        return "Employee [employee_id=" + employee_id + ", employeeName=" + employeeName + ", email=" + email
                + ", salary=" + salary + ", doj=" + doj + ", bonus=" + bonus + "]";
    }
}

4.4创建EmployeeDAO接口

package com.demos.dao;

import java.util.List;
import com.demos.model.Employee;
public interface EmployeeDAO {

    public abstract void createEmployee(Employee employee);
    public abstract Employee getEmployeeById(Integer employeeId);
    public abstract void updateEmployeeEmailById(String newEmail,Integer employeeId);
    public abstract void deleteEmployeeById(Integer employeeId);
    public abstract List<Employee> getAllEmployeesInfo();
}

4.5创建EmployeeDAOImpl实现EmployeeDAO接口

package com.demos.dao.impl;

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import com.demos.dao.EmployeeDAO;
import com.demos.model.Employee;
import com.demos.util.DBUtil;
import com.demos.dao.EmployeeDAO;
import com.demos.model.Employee;
import com.demos.util.DBUtil;

public class EmployeeDAOImpl implements EmployeeDAO {

    @Override
    public void createEmployee(Employee employee) {

        String SQL = "INSERT INTO employee_table(employee_name,email,salary,date_of_joining,bonus)VALUES(?,?,?,?,?)";
        try(Connection connection = DBUtil.getConnection(); PreparedStatement ps = connection.prepareStatement(SQL)) {

            ps.setString(1, employee.getEmployeeName());
            ps.setString(2, employee.getEmail());
            ps.setDouble(3, employee.getSalary());
            ps.setDate(4, new Date(employee.getDoj().getTime()));
            ps.setBigDecimal(5, employee.getBonus());

            int executeUpdate = ps.executeUpdate();

            if(executeUpdate ==1){
                System.out.println("Employee is crated..");
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    @Override
    public Employee getEmployeeById(Integer employeeId) {
        Employee employee = null;
        String SQL = "SELECT *FROM employee_table WHERE employee_id=?";
        try(Connection connection = DBUtil.getConnection();PreparedStatement ps = connection.prepareStatement(SQL)) {

            ps.setInt(1, employeeId);

            ResultSet rs = ps.executeQuery();
            if (rs.next()) {
                int empId = rs.getInt("employee_id");
                String eName = rs.getString("employee_name");
                String email = rs.getString("email");
                Double salary = rs.getDouble("salary");
                BigDecimal bonus = rs.getBigDecimal("bonus");
                Date date = rs.getDate("date_of_joining");

                employee = new Employee();
                employee.setEmployeeName(eName);
                employee.setBonus(bonus);
                employee.setDoj(date);
                employee.setEmail(email);
                employee.setEmployee_id(empId);
                employee.setSalary(salary);
            }

        } catch (Exception e) {
            e.printStackTrace();
        }

        return employee;
    }

    @Override
    public void updateEmployeeEmailById(String newEmail, Integer employeeId) {

        String SQL = "UPDATE employee_table set email=? WHERE employee_id=?;";
        try(Connection connection = DBUtil.getConnection();PreparedStatement ps = connection.prepareStatement(SQL)) {

            ps.setString(1, newEmail);
            ps.setInt(2,employeeId);

            int executeUpdate = ps.executeUpdate();

            if(executeUpdate ==1){
                System.out.println("Employee email is updated..");
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    @Override
    public void deleteEmployeeById(Integer employeeId) {

        String SQL = "DELETE FROM employee_table WHERE employee_id=?;";
        try(Connection connection = DBUtil.getConnection();PreparedStatement ps = connection.prepareStatement(SQL)) {

            ps.setInt(1,employeeId);

            int executeUpdate = ps.executeUpdate();

            if(executeUpdate ==1){
                System.out.println("Employee is deleted with ID::"+employeeId);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }

    }

    @Override
    public List<Employee> getAllEmployeesInfo() {

        List<Employee> empList = new ArrayList<>();
        String SQL = "SELECT *FROM employee_table";
        try(Connection connection = DBUtil.getConnection();PreparedStatement ps = connection.prepareStatement(SQL)) {

            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                int empId = rs.getInt("employee_id");
                String eName = rs.getString("employee_name");
                String email = rs.getString("email");
                Double salary = rs.getDouble("salary");
                BigDecimal bonus = rs.getBigDecimal("bonus");
                Date date = rs.getDate("date_of_joining");

                Employee employee = new Employee();
                employee.setEmployeeName(eName);
                employee.setBonus(bonus);
                employee.setDoj(date);
                employee.setEmail(email);
                employee.setEmployee_id(empId);
                employee.setSalary(salary);

                empList.add(employee);
            }

        } catch (Exception e) {
            e.printStackTrace();
        }
        return empList;
    }
}

4.6创建JDBCTest测试上述代码

package com.demos;

import java.math.BigDecimal;
import java.util.Date;
import java.util.List;

import com.demos.dao.EmployeeDAO;
import com.demos.dao.impl.EmployeeDAOImpl;
import com.demos.model.Employee;

public class JDBCTest {

    public static void main(String[] args) {
        try {
            EmployeeDAO employeeDAO = new EmployeeDAOImpl();
            Employee employee = getEmployee();
            employeeDAO.createEmployee(employee);
            //getEmployeeById(employeeDAO);
            //employeeDAO.updateEmployeeEmailById("sam.cs2014@gmail.com", 4);
            //employeeDAO.deleteEmployeeById(6);
            //getAllEmployeesInfo(employeeDAO);
        }
        catch (Exception ex) {
            String msg=ex.toString();
            String error=ex.toString();
        }
    }

    private static void getAllEmployeesInfo(EmployeeDAO employeeDAO) {
        List<Employee> empList = employeeDAO.getAllEmployeesInfo();
        for (Employee employee : empList) {
            System.out.println(employee);
        }
    }

    private static void getEmployeeById(EmployeeDAO employeeDAO) {
        Employee employee2 = employeeDAO.getEmployeeById(4);
        if (employee2 != null) {
            System.out.println(employee2);
        } else {
            System.out.println("Employee does not exist..");
        }
    }

    private static Employee getEmployee() {
        Employee employee = new Employee();
        employee.setBonus(new BigDecimal(290));
        employee.setDoj(new Date());
        employee.setEmployeeName("John");
        employee.setEmail("jonh.cs2016@yahoo.com");
        employee.setSalary(90000.00);
        return employee;
    }
}

回到顶部

5测试程序

在JDBCTest代码块中右击，选择Debug 'JDBCTest.main()'，开始调试程序

1使用IDEA创建maven工程

1.1打开idea，在首页选择Create New Project

1.2选择创建maven项目，设置SDK、勾选Create from archetype，点击next下一步

1.3设置项目路径、项目名称、包名等

1.4点击file->settings，设置maven home路径

1.5点击file->settings，设置Java Compiler版本

1.6点击file->Project Structures，设置jdk版本

回到顶部

2引入mysql依赖包

2.1在pom.xml中添加mysql依赖

<dependencies>
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>5.1.47</version>
        <scope>runtime</scope>
    </dependency>
</dependencies>

2.2在maven加载包

点击maven左上角的Reimport All Maven Projects 加载pox.xml中的jar包，之后Dependencies中会出现依赖的jar包

回到顶部

3准备数据

CREATE TABLE `employee_table` (
  `employee_id` int(11) NOT NULL auto_increment,
  `employee_name` varchar(60) NOT NULL,
  `email` varchar(45) NOT NULL,
  `salary` double default NULL,
  `date_of_joining` datetime default NULL,
  `bonus` decimal(10,0) default NULL,
  PRIMARY KEY  (`employee_id`)
) ;

回到顶部

4编写代码

4.1创建如下包名

com.demos、com.demos.util、com.demos.model、com.demos.dao、com.demos.dao.impl

4.2创建DBUtil工具类

package com.demos.util;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

public class DBUtil {
    private static final String DB_DRIVER_CLASS="com.mysql.jdbc.Driver";
    private static final String DB_USERNAME="root";
    private static final String DB_PASSWORD="123456";
    private static final String DB_URL ="jdbc:mysql://localhost:3306/demos";

    private static Connection connection = null;
    static{
        try {
            // 加载数据库驱动
            Class.forName(DB_DRIVER_CLASS);
            // 获取数据库连接
            connection = DriverManager.getConnection(DB_URL, DB_USERNAME, DB_PASSWORD);
        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        }
    }

    public static Connection getConnection(){
        return connection;
    }
}

4.3创建Employee实体类

package com.demos.model;

import java.math.BigDecimal;
import java.util.Date;

public class Employee {

    private int employee_id;
    private String employeeName;
    private String email;
    private Double salary;
    private Date doj;
    private BigDecimal bonus;

    public int getEmployee_id() {
        return employee_id;
    }
    public void setEmployee_id(int employee_id) {
        this.employee_id = employee_id;
    }
    public String getEmployeeName() {
        return employeeName;
    }
    public void setEmployeeName(String employeeName) {
        this.employeeName = employeeName;
    }
    public String getEmail() {
        return email;
    }
    public void setEmail(String email) {
        this.email = email;
    }
    public Double getSalary() {
        return salary;
    }
    public void setSalary(Double salary) {
        this.salary = salary;
    }
    public Date getDoj() {
        return doj;
    }
    public void setDoj(Date doj) {
        this.doj = doj;
    }
    public BigDecimal getBonus() {
        return bonus;
    }
    public void setBonus(BigDecimal bonus) {
        this.bonus = bonus;
    }
    @Override
    public String toString() {
        return "Employee [employee_id=" + employee_id + ", employeeName=" + employeeName + ", email=" + email
                + ", salary=" + salary + ", doj=" + doj + ", bonus=" + bonus + "]";
    }
}

4.4创建EmployeeDAO接口

package com.demos.dao;

import java.util.List;
import com.demos.model.Employee;
public interface EmployeeDAO {

    public abstract void createEmployee(Employee employee);
    public abstract Employee getEmployeeById(Integer employeeId);
    public abstract void updateEmployeeEmailById(String newEmail,Integer employeeId);
    public abstract void deleteEmployeeById(Integer employeeId);
    public abstract List<Employee> getAllEmployeesInfo();
}

4.5创建EmployeeDAOImpl实现EmployeeDAO接口

package com.demos.dao.impl;

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import com.demos.dao.EmployeeDAO;
import com.demos.model.Employee;
import com.demos.util.DBUtil;
import com.demos.dao.EmployeeDAO;
import com.demos.model.Employee;
import com.demos.util.DBUtil;

public class EmployeeDAOImpl implements EmployeeDAO {

    @Override
    public void createEmployee(Employee employee) {

        String SQL = "INSERT INTO employee_table(employee_name,email,salary,date_of_joining,bonus)VALUES(?,?,?,?,?)";
        try(Connection connection = DBUtil.getConnection(); PreparedStatement ps = connection.prepareStatement(SQL)) {

            ps.setString(1, employee.getEmployeeName());
            ps.setString(2, employee.getEmail());
            ps.setDouble(3, employee.getSalary());
            ps.setDate(4, new Date(employee.getDoj().getTime()));
            ps.setBigDecimal(5, employee.getBonus());

            int executeUpdate = ps.executeUpdate();

            if(executeUpdate ==1){
                System.out.println("Employee is crated..");
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    @Override
    public Employee getEmployeeById(Integer employeeId) {
        Employee employee = null;
        String SQL = "SELECT *FROM employee_table WHERE employee_id=?";
        try(Connection connection = DBUtil.getConnection();PreparedStatement ps = connection.prepareStatement(SQL)) {

            ps.setInt(1, employeeId);

            ResultSet rs = ps.executeQuery();
            if (rs.next()) {
                int empId = rs.getInt("employee_id");
                String eName = rs.getString("employee_name");
                String email = rs.getString("email");
                Double salary = rs.getDouble("salary");
                BigDecimal bonus = rs.getBigDecimal("bonus");
                Date date = rs.getDate("date_of_joining");

                employee = new Employee();
                employee.setEmployeeName(eName);
                employee.setBonus(bonus);
                employee.setDoj(date);
                employee.setEmail(email);
                employee.setEmployee_id(empId);
                employee.setSalary(salary);
            }

        } catch (Exception e) {
            e.printStackTrace();
        }

        return employee;
    }

    @Override
    public void updateEmployeeEmailById(String newEmail, Integer employeeId) {

        String SQL = "UPDATE employee_table set email=? WHERE employee_id=?;";
        try(Connection connection = DBUtil.getConnection();PreparedStatement ps = connection.prepareStatement(SQL)) {

            ps.setString(1, newEmail);
            ps.setInt(2,employeeId);

            int executeUpdate = ps.executeUpdate();

            if(executeUpdate ==1){
                System.out.println("Employee email is updated..");
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    @Override
    public void deleteEmployeeById(Integer employeeId) {

        String SQL = "DELETE FROM employee_table WHERE employee_id=?;";
        try(Connection connection = DBUtil.getConnection();PreparedStatement ps = connection.prepareStatement(SQL)) {

            ps.setInt(1,employeeId);

            int executeUpdate = ps.executeUpdate();

            if(executeUpdate ==1){
                System.out.println("Employee is deleted with ID::"+employeeId);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }

    }

    @Override
    public List<Employee> getAllEmployeesInfo() {

        List<Employee> empList = new ArrayList<>();
        String SQL = "SELECT *FROM employee_table";
        try(Connection connection = DBUtil.getConnection();PreparedStatement ps = connection.prepareStatement(SQL)) {

            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                int empId = rs.getInt("employee_id");
                String eName = rs.getString("employee_name");
                String email = rs.getString("email");
                Double salary = rs.getDouble("salary");
                BigDecimal bonus = rs.getBigDecimal("bonus");
                Date date = rs.getDate("date_of_joining");

                Employee employee = new Employee();
                employee.setEmployeeName(eName);
                employee.setBonus(bonus);
                employee.setDoj(date);
                employee.setEmail(email);
                employee.setEmployee_id(empId);
                employee.setSalary(salary);

                empList.add(employee);
            }

        } catch (Exception e) {
            e.printStackTrace();
        }
        return empList;
    }
}

4.6创建JDBCTest测试上述代码

package com.demos;

import java.math.BigDecimal;
import java.util.Date;
import java.util.List;

import com.demos.dao.EmployeeDAO;
import com.demos.dao.impl.EmployeeDAOImpl;
import com.demos.model.Employee;

public class JDBCTest {

    public static void main(String[] args) {
        try {
            EmployeeDAO employeeDAO = new EmployeeDAOImpl();
            Employee employee = getEmployee();
            employeeDAO.createEmployee(employee);
            //getEmployeeById(employeeDAO);
            //employeeDAO.updateEmployeeEmailById("sam.cs2014@gmail.com", 4);
            //employeeDAO.deleteEmployeeById(6);
            //getAllEmployeesInfo(employeeDAO);
        }
        catch (Exception ex) {
            String msg=ex.toString();
            String error=ex.toString();
        }
    }

    private static void getAllEmployeesInfo(EmployeeDAO employeeDAO) {
        List<Employee> empList = employeeDAO.getAllEmployeesInfo();
        for (Employee employee : empList) {
            System.out.println(employee);
        }
    }

    private static void getEmployeeById(EmployeeDAO employeeDAO) {
        Employee employee2 = employeeDAO.getEmployeeById(4);
        if (employee2 != null) {
            System.out.println(employee2);
        } else {
            System.out.println("Employee does not exist..");
        }
    }

    private static Employee getEmployee() {
        Employee employee = new Employee();
        employee.setBonus(new BigDecimal(290));
        employee.setDoj(new Date());
        employee.setEmployeeName("John");
        employee.setEmail("jonh.cs2016@yahoo.com");
        employee.setSalary(90000.00);
        return employee;
    }
}

回到顶部

5测试程序

在JDBCTest代码块中右击，选择Debug 'JDBCTest.main()'，开始调试程序

1使用IDEA创建maven工程

1.1打开idea，在首页选择Create New Project

1.2选择创建maven项目，设置SDK、勾选Create from archetype，点击next下一步

1.3设置项目路径、项目名称、包名等

1.4点击file->settings，设置maven home路径

1.5点击file->settings，设置Java Compiler版本

1.6点击file->Project Structures，设置jdk版本

回到顶部

2引入mysql依赖包

2.1在pom.xml中添加mysql依赖

<dependencies>
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>5.1.47</version>
        <scope>runtime</scope>
    </dependency>
</dependencies>

2.2在maven加载包

点击maven左上角的Reimport All Maven Projects 加载pox.xml中的jar包，之后Dependencies中会出现依赖的jar包

回到顶部

3准备数据

CREATE TABLE `employee_table` (
  `employee_id` int(11) NOT NULL auto_increment,
  `employee_name` varchar(60) NOT NULL,
  `email` varchar(45) NOT NULL,
  `salary` double default NULL,
  `date_of_joining` datetime default NULL,
  `bonus` decimal(10,0) default NULL,
  PRIMARY KEY  (`employee_id`)
) ;

回到顶部

4编写代码

4.1创建如下包名

com.demos、com.demos.util、com.demos.model、com.demos.dao、com.demos.dao.impl

4.2创建DBUtil工具类

package com.demos.util;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

public class DBUtil {
    private static final String DB_DRIVER_CLASS="com.mysql.jdbc.Driver";
    private static final String DB_USERNAME="root";
    private static final String DB_PASSWORD="123456";
    private static final String DB_URL ="jdbc:mysql://localhost:3306/demos";

    private static Connection connection = null;
    static{
        try {
            // 加载数据库驱动
            Class.forName(DB_DRIVER_CLASS);
            // 获取数据库连接
            connection = DriverManager.getConnection(DB_URL, DB_USERNAME, DB_PASSWORD);
        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        }
    }

    public static Connection getConnection(){
        return connection;
    }
}

4.3创建Employee实体类

package com.demos.model;

import java.math.BigDecimal;
import java.util.Date;

public class Employee {

    private int employee_id;
    private String employeeName;
    private String email;
    private Double salary;
    private Date doj;
    private BigDecimal bonus;

    public int getEmployee_id() {
        return employee_id;
    }
    public void setEmployee_id(int employee_id) {
        this.employee_id = employee_id;
    }
    public String getEmployeeName() {
        return employeeName;
    }
    public void setEmployeeName(String employeeName) {
        this.employeeName = employeeName;
    }
    public String getEmail() {
        return email;
    }
    public void setEmail(String email) {
        this.email = email;
    }
    public Double getSalary() {
        return salary;
    }
    public void setSalary(Double salary) {
        this.salary = salary;
    }
    public Date getDoj() {
        return doj;
    }
    public void setDoj(Date doj) {
        this.doj = doj;
    }
    public BigDecimal getBonus() {
        return bonus;
    }
    public void setBonus(BigDecimal bonus) {
        this.bonus = bonus;
    }
    @Override
    public String toString() {
        return "Employee [employee_id=" + employee_id + ", employeeName=" + employeeName + ", email=" + email
                + ", salary=" + salary + ", doj=" + doj + ", bonus=" + bonus + "]";
    }
}

4.4创建EmployeeDAO接口

package com.demos.dao;

import java.util.List;
import com.demos.model.Employee;
public interface EmployeeDAO {

    public abstract void createEmployee(Employee employee);
    public abstract Employee getEmployeeById(Integer employeeId);
    public abstract void updateEmployeeEmailById(String newEmail,Integer employeeId);
    public abstract void deleteEmployeeById(Integer employeeId);
    public abstract List<Employee> getAllEmployeesInfo();
}

4.5创建EmployeeDAOImpl实现EmployeeDAO接口

package com.demos.dao.impl;

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import com.demos.dao.EmployeeDAO;
import com.demos.model.Employee;
import com.demos.util.DBUtil;
import com.demos.dao.EmployeeDAO;
import com.demos.model.Employee;
import com.demos.util.DBUtil;

public class EmployeeDAOImpl implements EmployeeDAO {

    @Override
    public void createEmployee(Employee employee) {

        String SQL = "INSERT INTO employee_table(employee_name,email,salary,date_of_joining,bonus)VALUES(?,?,?,?,?)";
        try(Connection connection = DBUtil.getConnection(); PreparedStatement ps = connection.prepareStatement(SQL)) {

            ps.setString(1, employee.getEmployeeName());
            ps.setString(2, employee.getEmail());
            ps.setDouble(3, employee.getSalary());
            ps.setDate(4, new Date(employee.getDoj().getTime()));
            ps.setBigDecimal(5, employee.getBonus());

            int executeUpdate = ps.executeUpdate();

            if(executeUpdate ==1){
                System.out.println("Employee is crated..");
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    @Override
    public Employee getEmployeeById(Integer employeeId) {
        Employee employee = null;
        String SQL = "SELECT *FROM employee_table WHERE employee_id=?";
        try(Connection connection = DBUtil.getConnection();PreparedStatement ps = connection.prepareStatement(SQL)) {

            ps.setInt(1, employeeId);

            ResultSet rs = ps.executeQuery();
            if (rs.next()) {
                int empId = rs.getInt("employee_id");
                String eName = rs.getString("employee_name");
                String email = rs.getString("email");
                Double salary = rs.getDouble("salary");
                BigDecimal bonus = rs.getBigDecimal("bonus");
                Date date = rs.getDate("date_of_joining");

                employee = new Employee();
                employee.setEmployeeName(eName);
                employee.setBonus(bonus);
                employee.setDoj(date);
                employee.setEmail(email);
                employee.setEmployee_id(empId);
                employee.setSalary(salary);
            }

        } catch (Exception e) {
            e.printStackTrace();
        }

        return employee;
    }

    @Override
    public void updateEmployeeEmailById(String newEmail, Integer employeeId) {

        String SQL = "UPDATE employee_table set email=? WHERE employee_id=?;";
        try(Connection connection = DBUtil.getConnection();PreparedStatement ps = connection.prepareStatement(SQL)) {

            ps.setString(1, newEmail);
            ps.setInt(2,employeeId);

            int executeUpdate = ps.executeUpdate();

            if(executeUpdate ==1){
                System.out.println("Employee email is updated..");
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    @Override
    public void deleteEmployeeById(Integer employeeId) {

        String SQL = "DELETE FROM employee_table WHERE employee_id=?;";
        try(Connection connection = DBUtil.getConnection();PreparedStatement ps = connection.prepareStatement(SQL)) {

            ps.setInt(1,employeeId);

            int executeUpdate = ps.executeUpdate();

            if(executeUpdate ==1){
                System.out.println("Employee is deleted with ID::"+employeeId);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }

    }

    @Override
    public List<Employee> getAllEmployeesInfo() {

        List<Employee> empList = new ArrayList<>();
        String SQL = "SELECT *FROM employee_table";
        try(Connection connection = DBUtil.getConnection();PreparedStatement ps = connection.prepareStatement(SQL)) {

            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                int empId = rs.getInt("employee_id");
                String eName = rs.getString("employee_name");
                String email = rs.getString("email");
                Double salary = rs.getDouble("salary");
                BigDecimal bonus = rs.getBigDecimal("bonus");
                Date date = rs.getDate("date_of_joining");

                Employee employee = new Employee();
                employee.setEmployeeName(eName);
                employee.setBonus(bonus);
                employee.setDoj(date);
                employee.setEmail(email);
                employee.setEmployee_id(empId);
                employee.setSalary(salary);

                empList.add(employee);
            }

        } catch (Exception e) {
            e.printStackTrace();
        }
        return empList;
    }
}

4.6创建JDBCTest测试上述代码

package com.demos;

import java.math.BigDecimal;
import java.util.Date;
import java.util.List;

import com.demos.dao.EmployeeDAO;
import com.demos.dao.impl.EmployeeDAOImpl;
import com.demos.model.Employee;

public class JDBCTest {

    public static void main(String[] args) {
        try {
            EmployeeDAO employeeDAO = new EmployeeDAOImpl();
            Employee employee = getEmployee();
            employeeDAO.createEmployee(employee);
            //getEmployeeById(employeeDAO);
            //employeeDAO.updateEmployeeEmailById("sam.cs2014@gmail.com", 4);
            //employeeDAO.deleteEmployeeById(6);
            //getAllEmployeesInfo(employeeDAO);
        }
        catch (Exception ex) {
            String msg=ex.toString();
            String error=ex.toString();
        }
    }

    private static void getAllEmployeesInfo(EmployeeDAO employeeDAO) {
        List<Employee> empList = employeeDAO.getAllEmployeesInfo();
        for (Employee employee : empList) {
            System.out.println(employee);
        }
    }

    private static void getEmployeeById(EmployeeDAO employeeDAO) {
        Employee employee2 = employeeDAO.getEmployeeById(4);
        if (employee2 != null) {
            System.out.println(employee2);
        } else {
            System.out.println("Employee does not exist..");
        }
    }

    private static Employee getEmployee() {
        Employee employee = new Employee();
        employee.setBonus(new BigDecimal(290));
        employee.setDoj(new Date());
        employee.setEmployeeName("John");
        employee.setEmail("jonh.cs2016@yahoo.com");
        employee.setSalary(90000.00);
        return employee;
    }
}

回到顶部

5测试程序

在JDBCTest代码块中右击，选择Debug 'JDBCTest.main()'，开始调试程序

1使用IDEA创建maven工程

1.1打开idea，在首页选择Create New Project

1.2选择创建maven项目，设置SDK、勾选Create from archetype，点击next下一步

1.3设置项目路径、项目名称、包名等

1.4点击file->settings，设置maven home路径

1.5点击file->settings，设置Java Compiler版本

1.6点击file->Project Structures，设置jdk版本

回到顶部

2引入mysql依赖包

2.1在pom.xml中添加mysql依赖

<dependencies>
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>5.1.47</version>
        <scope>runtime</scope>
    </dependency>
</dependencies>

2.2在maven加载包

点击maven左上角的Reimport All Maven Projects 加载pox.xml中的jar包，之后Dependencies中会出现依赖的jar包

回到顶部

3准备数据

CREATE TABLE `employee_table` (
  `employee_id` int(11) NOT NULL auto_increment,
  `employee_name` varchar(60) NOT NULL,
  `email` varchar(45) NOT NULL,
  `salary` double default NULL,
  `date_of_joining` datetime default NULL,
  `bonus` decimal(10,0) default NULL,
  PRIMARY KEY  (`employee_id`)
) ;

回到顶部

4编写代码

4.1创建如下包名

com.demos、com.demos.util、com.demos.model、com.demos.dao、com.demos.dao.impl

4.2创建DBUtil工具类

package com.demos.util;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

public class DBUtil {
    private static final String DB_DRIVER_CLASS="com.mysql.jdbc.Driver";
    private static final String DB_USERNAME="root";
    private static final String DB_PASSWORD="123456";
    private static final String DB_URL ="jdbc:mysql://localhost:3306/demos";

    private static Connection connection = null;
    static{
        try {
            // 加载数据库驱动
            Class.forName(DB_DRIVER_CLASS);
            // 获取数据库连接
            connection = DriverManager.getConnection(DB_URL, DB_USERNAME, DB_PASSWORD);
        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        }
    }

    public static Connection getConnection(){
        return connection;
    }
}

4.3创建Employee实体类

package com.demos.model;

import java.math.BigDecimal;
import java.util.Date;

public class Employee {

    private int employee_id;
    private String employeeName;
    private String email;
    private Double salary;
    private Date doj;
    private BigDecimal bonus;

    public int getEmployee_id() {
        return employee_id;
    }
    public void setEmployee_id(int employee_id) {
        this.employee_id = employee_id;
    }
    public String getEmployeeName() {
        return employeeName;
    }
    public void setEmployeeName(String employeeName) {
        this.employeeName = employeeName;
    }
    public String getEmail() {
        return email;
    }
    public void setEmail(String email) {
        this.email = email;
    }
    public Double getSalary() {
        return salary;
    }
    public void setSalary(Double salary) {
        this.salary = salary;
    }
    public Date getDoj() {
        return doj;
    }
    public void setDoj(Date doj) {
        this.doj = doj;
    }
    public BigDecimal getBonus() {
        return bonus;
    }
    public void setBonus(BigDecimal bonus) {
        this.bonus = bonus;
    }
    @Override
    public String toString() {
        return "Employee [employee_id=" + employee_id + ", employeeName=" + employeeName + ", email=" + email
                + ", salary=" + salary + ", doj=" + doj + ", bonus=" + bonus + "]";
    }
}

4.4创建EmployeeDAO接口

package com.demos.dao;

import java.util.List;
import com.demos.model.Employee;
public interface EmployeeDAO {

    public abstract void createEmployee(Employee employee);
    public abstract Employee getEmployeeById(Integer employeeId);
    public abstract void updateEmployeeEmailById(String newEmail,Integer employeeId);
    public abstract void deleteEmployeeById(Integer employeeId);
    public abstract List<Employee> getAllEmployeesInfo();
}

4.5创建EmployeeDAOImpl实现EmployeeDAO接口

package com.demos.dao.impl;

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import com.demos.dao.EmployeeDAO;
import com.demos.model.Employee;
import com.demos.util.DBUtil;
import com.demos.dao.EmployeeDAO;
import com.demos.model.Employee;
import com.demos.util.DBUtil;

public class EmployeeDAOImpl implements EmployeeDAO {

    @Override
    public void createEmployee(Employee employee) {

        String SQL = "INSERT INTO employee_table(employee_name,email,salary,date_of_joining,bonus)VALUES(?,?,?,?,?)";
        try(Connection connection = DBUtil.getConnection(); PreparedStatement ps = connection.prepareStatement(SQL)) {

            ps.setString(1, employee.getEmployeeName());
            ps.setString(2, employee.getEmail());
            ps.setDouble(3, employee.getSalary());
            ps.setDate(4, new Date(employee.getDoj().getTime()));
            ps.setBigDecimal(5, employee.getBonus());

            int executeUpdate = ps.executeUpdate();

            if(executeUpdate ==1){
                System.out.println("Employee is crated..");
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    @Override
    public Employee getEmployeeById(Integer employeeId) {
        Employee employee = null;
        String SQL = "SELECT *FROM employee_table WHERE employee_id=?";
        try(Connection connection = DBUtil.getConnection();PreparedStatement ps = connection.prepareStatement(SQL)) {

            ps.setInt(1, employeeId);

            ResultSet rs = ps.executeQuery();
            if (rs.next()) {
                int empId = rs.getInt("employee_id");
                String eName = rs.getString("employee_name");
                String email = rs.getString("email");
                Double salary = rs.getDouble("salary");
                BigDecimal bonus = rs.getBigDecimal("bonus");
                Date date = rs.getDate("date_of_joining");

                employee = new Employee();
                employee.setEmployeeName(eName);
                employee.setBonus(bonus);
                employee.setDoj(date);
                employee.setEmail(email);
                employee.setEmployee_id(empId);
                employee.setSalary(salary);
            }

        } catch (Exception e) {
            e.printStackTrace();
        }

        return employee;
    }

    @Override
    public void updateEmployeeEmailById(String newEmail, Integer employeeId) {

        String SQL = "UPDATE employee_table set email=? WHERE employee_id=?;";
        try(Connection connection = DBUtil.getConnection();PreparedStatement ps = connection.prepareStatement(SQL)) {

            ps.setString(1, newEmail);
            ps.setInt(2,employeeId);

            int executeUpdate = ps.executeUpdate();

            if(executeUpdate ==1){
                System.out.println("Employee email is updated..");
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    @Override
    public void deleteEmployeeById(Integer employeeId) {

        String SQL = "DELETE FROM employee_table WHERE employee_id=?;";
        try(Connection connection = DBUtil.getConnection();PreparedStatement ps = connection.prepareStatement(SQL)) {

            ps.setInt(1,employeeId);

            int executeUpdate = ps.executeUpdate();

            if(executeUpdate ==1){
                System.out.println("Employee is deleted with ID::"+employeeId);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }

    }

    @Override
    public List<Employee> getAllEmployeesInfo() {

        List<Employee> empList = new ArrayList<>();
        String SQL = "SELECT *FROM employee_table";
        try(Connection connection = DBUtil.getConnection();PreparedStatement ps = connection.prepareStatement(SQL)) {

            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                int empId = rs.getInt("employee_id");
                String eName = rs.getString("employee_name");
                String email = rs.getString("email");
                Double salary = rs.getDouble("salary");
                BigDecimal bonus = rs.getBigDecimal("bonus");
                Date date = rs.getDate("date_of_joining");

                Employee employee = new Employee();
                employee.setEmployeeName(eName);
                employee.setBonus(bonus);
                employee.setDoj(date);
                employee.setEmail(email);
                employee.setEmployee_id(empId);
                employee.setSalary(salary);

                empList.add(employee);
            }

        } catch (Exception e) {
            e.printStackTrace();
        }
        return empList;
    }
}

4.6创建JDBCTest测试上述代码

package com.demos;

import java.math.BigDecimal;
import java.util.Date;
import java.util.List;

import com.demos.dao.EmployeeDAO;
import com.demos.dao.impl.EmployeeDAOImpl;
import com.demos.model.Employee;

public class JDBCTest {

    public static void main(String[] args) {
        try {
            EmployeeDAO employeeDAO = new EmployeeDAOImpl();
            Employee employee = getEmployee();
            employeeDAO.createEmployee(employee);
            //getEmployeeById(employeeDAO);
            //employeeDAO.updateEmployeeEmailById("sam.cs2014@gmail.com", 4);
            //employeeDAO.deleteEmployeeById(6);
            //getAllEmployeesInfo(employeeDAO);
        }
        catch (Exception ex) {
            String msg=ex.toString();
            String error=ex.toString();
        }
    }

    private static void getAllEmployeesInfo(EmployeeDAO employeeDAO) {
        List<Employee> empList = employeeDAO.getAllEmployeesInfo();
        for (Employee employee : empList) {
            System.out.println(employee);
        }
    }

    private static void getEmployeeById(EmployeeDAO employeeDAO) {
        Employee employee2 = employeeDAO.getEmployeeById(4);
        if (employee2 != null) {
            System.out.println(employee2);
        } else {
            System.out.println("Employee does not exist..");
        }
    }

    private static Employee getEmployee() {
        Employee employee = new Employee();
        employee.setBonus(new BigDecimal(290));
        employee.setDoj(new Date());
        employee.setEmployeeName("John");
        employee.setEmail("jonh.cs2016@yahoo.com");
        employee.setSalary(90000.00);
        return employee;
    }
}

回到顶部

5测试程序

在JDBCTest代码块中右击，选择Debug 'JDBCTest.main()'，开始调试程序

1使用IDEA创建maven工程

1.1打开idea，在首页选择Create New Project

1.2选择创建maven项目，设置SDK、勾选Create from archetype，点击next下一步

1.3设置项目路径、项目名称、包名等

1.4点击file->settings，设置maven home路径

1.5点击file->settings，设置Java Compiler版本

1.6点击file->Project Structures，设置jdk版本

回到顶部

2引入mysql依赖包

2.1在pom.xml中添加mysql依赖

<dependencies>
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>5.1.47</version>
        <scope>runtime</scope>
    </dependency>
</dependencies>

2.2在maven加载包

点击maven左上角的Reimport All Maven Projects 加载pox.xml中的jar包，之后Dependencies中会出现依赖的jar包

回到顶部

3准备数据

CREATE TABLE `employee_table` (
  `employee_id` int(11) NOT NULL auto_increment,
  `employee_name` varchar(60) NOT NULL,
  `email` varchar(45) NOT NULL,
  `salary` double default NULL,
  `date_of_joining` datetime default NULL,
  `bonus` decimal(10,0) default NULL,
  PRIMARY KEY  (`employee_id`)
) ;

回到顶部

4编写代码

4.1创建如下包名

com.demos、com.demos.util、com.demos.model、com.demos.dao、com.demos.dao.impl

4.2创建DBUtil工具类

package com.demos.util;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

public class DBUtil {
    private static final String DB_DRIVER_CLASS="com.mysql.jdbc.Driver";
    private static final String DB_USERNAME="root";
    private static final String DB_PASSWORD="123456";
    private static final String DB_URL ="jdbc:mysql://localhost:3306/demos";

    private static Connection connection = null;
    static{
        try {
            // 加载数据库驱动
            Class.forName(DB_DRIVER_CLASS);
            // 获取数据库连接
            connection = DriverManager.getConnection(DB_URL, DB_USERNAME, DB_PASSWORD);
        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        }
    }

    public static Connection getConnection(){
        return connection;
    }
}

4.3创建Employee实体类

package com.demos.model;

import java.math.BigDecimal;
import java.util.Date;

public class Employee {

    private int employee_id;
    private String employeeName;
    private String email;
    private Double salary;
    private Date doj;
    private BigDecimal bonus;

    public int getEmployee_id() {
        return employee_id;
    }
    public void setEmployee_id(int employee_id) {
        this.employee_id = employee_id;
    }
    public String getEmployeeName() {
        return employeeName;
    }
    public void setEmployeeName(String employeeName) {
        this.employeeName = employeeName;
    }
    public String getEmail() {
        return email;
    }
    public void setEmail(String email) {
        this.email = email;
    }
    public Double getSalary() {
        return salary;
    }
    public void setSalary(Double salary) {
        this.salary = salary;
    }
    public Date getDoj() {
        return doj;
    }
    public void setDoj(Date doj) {
        this.doj = doj;
    }
    public BigDecimal getBonus() {
        return bonus;
    }
    public void setBonus(BigDecimal bonus) {
        this.bonus = bonus;
    }
    @Override
    public String toString() {
        return "Employee [employee_id=" + employee_id + ", employeeName=" + employeeName + ", email=" + email
                + ", salary=" + salary + ", doj=" + doj + ", bonus=" + bonus + "]";
    }
}

4.4创建EmployeeDAO接口

package com.demos.dao;

import java.util.List;
import com.demos.model.Employee;
public interface EmployeeDAO {

    public abstract void createEmployee(Employee employee);
    public abstract Employee getEmployeeById(Integer employeeId);
    public abstract void updateEmployeeEmailById(String newEmail,Integer employeeId);
    public abstract void deleteEmployeeById(Integer employeeId);
    public abstract List<Employee> getAllEmployeesInfo();
}

4.5创建EmployeeDAOImpl实现EmployeeDAO接口

package com.demos.dao.impl;

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import com.demos.dao.EmployeeDAO;
import com.demos.model.Employee;
import com.demos.util.DBUtil;
import com.demos.dao.EmployeeDAO;
import com.demos.model.Employee;
import com.demos.util.DBUtil;

public class EmployeeDAOImpl implements EmployeeDAO {

    @Override
    public void createEmployee(Employee employee) {

        String SQL = "INSERT INTO employee_table(employee_name,email,salary,date_of_joining,bonus)VALUES(?,?,?,?,?)";
        try(Connection connection = DBUtil.getConnection(); PreparedStatement ps = connection.prepareStatement(SQL)) {

            ps.setString(1, employee.getEmployeeName());
            ps.setString(2, employee.getEmail());
            ps.setDouble(3, employee.getSalary());
            ps.setDate(4, new Date(employee.getDoj().getTime()));
            ps.setBigDecimal(5, employee.getBonus());

            int executeUpdate = ps.executeUpdate();

            if(executeUpdate ==1){
                System.out.println("Employee is crated..");
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    @Override
    public Employee getEmployeeById(Integer employeeId) {
        Employee employee = null;
        String SQL = "SELECT *FROM employee_table WHERE employee_id=?";
        try(Connection connection = DBUtil.getConnection();PreparedStatement ps = connection.prepareStatement(SQL)) {

            ps.setInt(1, employeeId);

            ResultSet rs = ps.executeQuery();
            if (rs.next()) {
                int empId = rs.getInt("employee_id");
                String eName = rs.getString("employee_name");
                String email = rs.getString("email");
                Double salary = rs.getDouble("salary");
                BigDecimal bonus = rs.getBigDecimal("bonus");
                Date date = rs.getDate("date_of_joining");

                employee = new Employee();
                employee.setEmployeeName(eName);
                employee.setBonus(bonus);
                employee.setDoj(date);
                employee.setEmail(email);
                employee.setEmployee_id(empId);
                employee.setSalary(salary);
            }

        } catch (Exception e) {
            e.printStackTrace();
        }

        return employee;
    }

    @Override
    public void updateEmployeeEmailById(String newEmail, Integer employeeId) {

        String SQL = "UPDATE employee_table set email=? WHERE employee_id=?;";
        try(Connection connection = DBUtil.getConnection();PreparedStatement ps = connection.prepareStatement(SQL)) {

            ps.setString(1, newEmail);
            ps.setInt(2,employeeId);

            int executeUpdate = ps.executeUpdate();

            if(executeUpdate ==1){
                System.out.println("Employee email is updated..");
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    @Override
    public void deleteEmployeeById(Integer employeeId) {

        String SQL = "DELETE FROM employee_table WHERE employee_id=?;";
        try(Connection connection = DBUtil.getConnection();PreparedStatement ps = connection.prepareStatement(SQL)) {

            ps.setInt(1,employeeId);

            int executeUpdate = ps.executeUpdate();

            if(executeUpdate ==1){
                System.out.println("Employee is deleted with ID::"+employeeId);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }

    }

    @Override
    public List<Employee> getAllEmployeesInfo() {

        List<Employee> empList = new ArrayList<>();
        String SQL = "SELECT *FROM employee_table";
        try(Connection connection = DBUtil.getConnection();PreparedStatement ps = connection.prepareStatement(SQL)) {

            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                int empId = rs.getInt("employee_id");
                String eName = rs.getString("employee_name");
                String email = rs.getString("email");
                Double salary = rs.getDouble("salary");
                BigDecimal bonus = rs.getBigDecimal("bonus");
                Date date = rs.getDate("date_of_joining");

                Employee employee = new Employee();
                employee.setEmployeeName(eName);
                employee.setBonus(bonus);
                employee.setDoj(date);
                employee.setEmail(email);
                employee.setEmployee_id(empId);
                employee.setSalary(salary);

                empList.add(employee);
            }

        } catch (Exception e) {
            e.printStackTrace();
        }
        return empList;
    }
}

4.6创建JDBCTest测试上述代码

package com.demos;

import java.math.BigDecimal;
import java.util.Date;
import java.util.List;

import com.demos.dao.EmployeeDAO;
import com.demos.dao.impl.EmployeeDAOImpl;
import com.demos.model.Employee;

public class JDBCTest {

    public static void main(String[] args) {
        try {
            EmployeeDAO employeeDAO = new EmployeeDAOImpl();
            Employee employee = getEmployee();
            employeeDAO.createEmployee(employee);
            //getEmployeeById(employeeDAO);
            //employeeDAO.updateEmployeeEmailById("sam.cs2014@gmail.com", 4);
            //employeeDAO.deleteEmployeeById(6);
            //getAllEmployeesInfo(employeeDAO);
        }
        catch (Exception ex) {
            String msg=ex.toString();
            String error=ex.toString();
        }
    }

    private static void getAllEmployeesInfo(EmployeeDAO employeeDAO) {
        List<Employee> empList = employeeDAO.getAllEmployeesInfo();
        for (Employee employee : empList) {
            System.out.println(employee);
        }
    }

    private static void getEmployeeById(EmployeeDAO employeeDAO) {
        Employee employee2 = employeeDAO.getEmployeeById(4);
        if (employee2 != null) {
            System.out.println(employee2);
        } else {
            System.out.println("Employee does not exist..");
        }
    }

    private static Employee getEmployee() {
        Employee employee = new Employee();
        employee.setBonus(new BigDecimal(290));
        employee.setDoj(new Date());
        employee.setEmployeeName("John");
        employee.setEmail("jonh.cs2016@yahoo.com");
        employee.setSalary(90000.00);
        return employee;
    }
}

回到顶部

5测试程序

在JDBCTest代码块中右击，选择Debug 'JDBCTest.main()'，开始调试程序

1使用IDEA创建maven工程

1.1打开idea，在首页选择Create New Project

1.2选择创建maven项目，设置SDK、勾选Create from archetype，点击next下一步

1.3设置项目路径、项目名称、包名等

1.4点击file->settings，设置maven home路径

1.5点击file->settings，设置Java Compiler版本

1.6点击file->Project Structures，设置jdk版本

回到顶部

2引入mysql依赖包

2.1在pom.xml中添加mysql依赖

<dependencies>
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>5.1.47</version>
        <scope>runtime</scope>
    </dependency>
</dependencies>

2.2在maven加载包

点击maven左上角的Reimport All Maven Projects 加载pox.xml中的jar包，之后Dependencies中会出现依赖的jar包

回到顶部

3准备数据

CREATE TABLE `employee_table` (
  `employee_id` int(11) NOT NULL auto_increment,
  `employee_name` varchar(60) NOT NULL,
  `email` varchar(45) NOT NULL,
  `salary` double default NULL,
  `date_of_joining` datetime default NULL,
  `bonus` decimal(10,0) default NULL,
  PRIMARY KEY  (`employee_id`)
) ;

回到顶部

4编写代码

4.1创建如下包名

com.demos、com.demos.util、com.demos.model、com.demos.dao、com.demos.dao.impl

4.2创建DBUtil工具类

package com.demos.util;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

public class DBUtil {
    private static final String DB_DRIVER_CLASS="com.mysql.jdbc.Driver";
    private static final String DB_USERNAME="root";
    private static final String DB_PASSWORD="123456";
    private static final String DB_URL ="jdbc:mysql://localhost:3306/demos";

    private static Connection connection = null;
    static{
        try {
            // 加载数据库驱动
            Class.forName(DB_DRIVER_CLASS);
            // 获取数据库连接
            connection = DriverManager.getConnection(DB_URL, DB_USERNAME, DB_PASSWORD);
        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        }
    }

    public static Connection getConnection(){
        return connection;
    }
}

4.3创建Employee实体类

package com.demos.model;

import java.math.BigDecimal;
import java.util.Date;

public class Employee {

    private int employee_id;
    private String employeeName;
    private String email;
    private Double salary;
    private Date doj;
    private BigDecimal bonus;

    public int getEmployee_id() {
        return employee_id;
    }
    public void setEmployee_id(int employee_id) {
        this.employee_id = employee_id;
    }
    public String getEmployeeName() {
        return employeeName;
    }
    public void setEmployeeName(String employeeName) {
        this.employeeName = employeeName;
    }
    public String getEmail() {
        return email;
    }
    public void setEmail(String email) {
        this.email = email;
    }
    public Double getSalary() {
        return salary;
    }
    public void setSalary(Double salary) {
        this.salary = salary;
    }
    public Date getDoj() {
        return doj;
    }
    public void setDoj(Date doj) {
        this.doj = doj;
    }
    public BigDecimal getBonus() {
        return bonus;
    }
    public void setBonus(BigDecimal bonus) {
        this.bonus = bonus;
    }
    @Override
    public String toString() {
        return "Employee [employee_id=" + employee_id + ", employeeName=" + employeeName + ", email=" + email
                + ", salary=" + salary + ", doj=" + doj + ", bonus=" + bonus + "]";
    }
}

4.4创建EmployeeDAO接口

package com.demos.dao;

import java.util.List;
import com.demos.model.Employee;
public interface EmployeeDAO {

    public abstract void createEmployee(Employee employee);
    public abstract Employee getEmployeeById(Integer employeeId);
    public abstract void updateEmployeeEmailById(String newEmail,Integer employeeId);
    public abstract void deleteEmployeeById(Integer employeeId);
    public abstract List<Employee> getAllEmployeesInfo();
}

4.5创建EmployeeDAOImpl实现EmployeeDAO接口

package com.demos.dao.impl;

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import com.demos.dao.EmployeeDAO;
import com.demos.model.Employee;
import com.demos.util.DBUtil;
import com.demos.dao.EmployeeDAO;
import com.demos.model.Employee;
import com.demos.util.DBUtil;

public class EmployeeDAOImpl implements EmployeeDAO {

    @Override
    public void createEmployee(Employee employee) {

        String SQL = "INSERT INTO employee_table(employee_name,email,salary,date_of_joining,bonus)VALUES(?,?,?,?,?)";
        try(Connection connection = DBUtil.getConnection(); PreparedStatement ps = connection.prepareStatement(SQL)) {

            ps.setString(1, employee.getEmployeeName());
            ps.setString(2, employee.getEmail());
            ps.setDouble(3, employee.getSalary());
            ps.setDate(4, new Date(employee.getDoj().getTime()));
            ps.setBigDecimal(5, employee.getBonus());

            int executeUpdate = ps.executeUpdate();

            if(executeUpdate ==1){
                System.out.println("Employee is crated..");
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    @Override
    public Employee getEmployeeById(Integer employeeId) {
        Employee employee = null;
        String SQL = "SELECT *FROM employee_table WHERE employee_id=?";
        try(Connection connection = DBUtil.getConnection();PreparedStatement ps = connection.prepareStatement(SQL)) {

            ps.setInt(1, employeeId);

            ResultSet rs = ps.executeQuery();
            if (rs.next()) {
                int empId = rs.getInt("employee_id");
                String eName = rs.getString("employee_name");
                String email = rs.getString("email");
                Double salary = rs.getDouble("salary");
                BigDecimal bonus = rs.getBigDecimal("bonus");
                Date date = rs.getDate("date_of_joining");

                employee = new Employee();
                employee.setEmployeeName(eName);
                employee.setBonus(bonus);
                employee.setDoj(date);
                employee.setEmail(email);
                employee.setEmployee_id(empId);
                employee.setSalary(salary);
            }

        } catch (Exception e) {
            e.printStackTrace();
        }

        return employee;
    }

    @Override
    public void updateEmployeeEmailById(String newEmail, Integer employeeId) {

        String SQL = "UPDATE employee_table set email=? WHERE employee_id=?;";
        try(Connection connection = DBUtil.getConnection();PreparedStatement ps = connection.prepareStatement(SQL)) {

            ps.setString(1, newEmail);
            ps.setInt(2,employeeId);

            int executeUpdate = ps.executeUpdate();

            if(executeUpdate ==1){
                System.out.println("Employee email is updated..");
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    @Override
    public void deleteEmployeeById(Integer employeeId) {

        String SQL = "DELETE FROM employee_table WHERE employee_id=?;";
        try(Connection connection = DBUtil.getConnection();PreparedStatement ps = connection.prepareStatement(SQL)) {

            ps.setInt(1,employeeId);

            int executeUpdate = ps.executeUpdate();

            if(executeUpdate ==1){
                System.out.println("Employee is deleted with ID::"+employeeId);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }

    }

    @Override
    public List<Employee> getAllEmployeesInfo() {

        List<Employee> empList = new ArrayList<>();
        String SQL = "SELECT *FROM employee_table";
        try(Connection connection = DBUtil.getConnection();PreparedStatement ps = connection.prepareStatement(SQL)) {

            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                int empId = rs.getInt("employee_id");
                String eName = rs.getString("employee_name");
                String email = rs.getString("email");
                Double salary = rs.getDouble("salary");
                BigDecimal bonus = rs.getBigDecimal("bonus");
                Date date = rs.getDate("date_of_joining");

                Employee employee = new Employee();
                employee.setEmployeeName(eName);
                employee.setBonus(bonus);
                employee.setDoj(date);
                employee.setEmail(email);
                employee.setEmployee_id(empId);
                employee.setSalary(salary);

                empList.add(employee);
            }

        } catch (Exception e) {
            e.printStackTrace();
        }
        return empList;
    }
}

4.6创建JDBCTest测试上述代码

package com.demos;

import java.math.BigDecimal;
import java.util.Date;
import java.util.List;

import com.demos.dao.EmployeeDAO;
import com.demos.dao.impl.EmployeeDAOImpl;
import com.demos.model.Employee;

public class JDBCTest {

    public static void main(String[] args) {
        try {
            EmployeeDAO employeeDAO = new EmployeeDAOImpl();
            Employee employee = getEmployee();
            employeeDAO.createEmployee(employee);
            //getEmployeeById(employeeDAO);
            //employeeDAO.updateEmployeeEmailById("sam.cs2014@gmail.com", 4);
            //employeeDAO.deleteEmployeeById(6);
            //getAllEmployeesInfo(employeeDAO);
        }
        catch (Exception ex) {
            String msg=ex.toString();
            String error=ex.toString();
        }
    }

    private static void getAllEmployeesInfo(EmployeeDAO employeeDAO) {
        List<Employee> empList = employeeDAO.getAllEmployeesInfo();
        for (Employee employee : empList) {
            System.out.println(employee);
        }
    }

    private static void getEmployeeById(EmployeeDAO employeeDAO) {
        Employee employee2 = employeeDAO.getEmployeeById(4);
        if (employee2 != null) {
            System.out.println(employee2);
        } else {
            System.out.println("Employee does not exist..");
        }
    }

    private static Employee getEmployee() {
        Employee employee = new Employee();
        employee.setBonus(new BigDecimal(290));
        employee.setDoj(new Date());
        employee.setEmployeeName("John");
        employee.setEmail("jonh.cs2016@yahoo.com");
        employee.setSalary(90000.00);
        return employee;
    }
}

回到顶部

5测试程序

在JDBCTest代码块中右击，选择Debug 'JDBCTest.main()'，开始调试程序

1使用IDEA创建maven工程

1.1打开idea，在首页选择Create New Project

1.2选择创建maven项目，设置SDK、勾选Create from archetype，点击next下一步

1.3设置项目路径、项目名称、包名等

1.4点击file->settings，设置maven home路径

1.5点击file->settings，设置Java Compiler版本

1.6点击file->Project Structures，设置jdk版本

回到顶部

2引入mysql依赖包

2.1在pom.xml中添加mysql依赖

<dependencies>
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>5.1.47</version>
        <scope>runtime</scope>
    </dependency>
</dependencies>

2.2在maven加载包

点击maven左上角的Reimport All Maven Projects 加载pox.xml中的jar包，之后Dependencies中会出现依赖的jar包

回到顶部

3准备数据

CREATE TABLE `employee_table` (
  `employee_id` int(11) NOT NULL auto_increment,
  `employee_name` varchar(60) NOT NULL,
  `email` varchar(45) NOT NULL,
  `salary` double default NULL,
  `date_of_joining` datetime default NULL,
  `bonus` decimal(10,0) default NULL,
  PRIMARY KEY  (`employee_id`)
) ;

回到顶部

4编写代码

4.1创建如下包名

com.demos、com.demos.util、com.demos.model、com.demos.dao、com.demos.dao.impl

4.2创建DBUtil工具类

package com.demos.util;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

public class DBUtil {
    private static final String DB_DRIVER_CLASS="com.mysql.jdbc.Driver";
    private static final String DB_USERNAME="root";
    private static final String DB_PASSWORD="123456";
    private static final String DB_URL ="jdbc:mysql://localhost:3306/demos";

    private static Connection connection = null;
    static{
        try {
            // 加载数据库驱动
            Class.forName(DB_DRIVER_CLASS);
            // 获取数据库连接
            connection = DriverManager.getConnection(DB_URL, DB_USERNAME, DB_PASSWORD);
        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        }
    }

    public static Connection getConnection(){
        return connection;
    }
}

4.3创建Employee实体类

package com.demos.model;

import java.math.BigDecimal;
import java.util.Date;

public class Employee {

    private int employee_id;
    private String employeeName;
    private String email;
    private Double salary;
    private Date doj;
    private BigDecimal bonus;

    public int getEmployee_id() {
        return employee_id;
    }
    public void setEmployee_id(int employee_id) {
        this.employee_id = employee_id;
    }
    public String getEmployeeName() {
        return employeeName;
    }
    public void setEmployeeName(String employeeName) {
        this.employeeName = employeeName;
    }
    public String getEmail() {
        return email;
    }
    public void setEmail(String email) {
        this.email = email;
    }
    public Double getSalary() {
        return salary;
    }
    public void setSalary(Double salary) {
        this.salary = salary;
    }
    public Date getDoj() {
        return doj;
    }
    public void setDoj(Date doj) {
        this.doj = doj;
    }
    public BigDecimal getBonus() {
        return bonus;
    }
    public void setBonus(BigDecimal bonus) {
        this.bonus = bonus;
    }
    @Override
    public String toString() {
        return "Employee [employee_id=" + employee_id + ", employeeName=" + employeeName + ", email=" + email
                + ", salary=" + salary + ", doj=" + doj + ", bonus=" + bonus + "]";
    }
}

4.4创建EmployeeDAO接口

package com.demos.dao;

import java.util.List;
import com.demos.model.Employee;
public interface EmployeeDAO {

    public abstract void createEmployee(Employee employee);
    public abstract Employee getEmployeeById(Integer employeeId);
    public abstract void updateEmployeeEmailById(String newEmail,Integer employeeId);
    public abstract void deleteEmployeeById(Integer employeeId);
    public abstract List<Employee> getAllEmployeesInfo();
}

4.5创建EmployeeDAOImpl实现EmployeeDAO接口

package com.demos.dao.impl;

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import com.demos.dao.EmployeeDAO;
import com.demos.model.Employee;
import com.demos.util.DBUtil;
import com.demos.dao.EmployeeDAO;
import com.demos.model.Employee;
import com.demos.util.DBUtil;

public class EmployeeDAOImpl implements EmployeeDAO {

    @Override
    public void createEmployee(Employee employee) {

        String SQL = "INSERT INTO employee_table(employee_name,email,salary,date_of_joining,bonus)VALUES(?,?,?,?,?)";
        try(Connection connection = DBUtil.getConnection(); PreparedStatement ps = connection.prepareStatement(SQL)) {

            ps.setString(1, employee.getEmployeeName());
            ps.setString(2, employee.getEmail());
            ps.setDouble(3, employee.getSalary());
            ps.setDate(4, new Date(employee.getDoj().getTime()));
            ps.setBigDecimal(5, employee.getBonus());

            int executeUpdate = ps.executeUpdate();

            if(executeUpdate ==1){
                System.out.println("Employee is crated..");
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    @Override
    public Employee getEmployeeById(Integer employeeId) {
        Employee employee = null;
        String SQL = "SELECT *FROM employee_table WHERE employee_id=?";
        try(Connection connection = DBUtil.getConnection();PreparedStatement ps = connection.prepareStatement(SQL)) {

            ps.setInt(1, employeeId);

            ResultSet rs = ps.executeQuery();
            if (rs.next()) {
                int empId = rs.getInt("employee_id");
                String eName = rs.getString("employee_name");
                String email = rs.getString("email");
                Double salary = rs.getDouble("salary");
                BigDecimal bonus = rs.getBigDecimal("bonus");
                Date date = rs.getDate("date_of_joining");

                employee = new Employee();
                employee.setEmployeeName(eName);
                employee.setBonus(bonus);
                employee.setDoj(date);
                employee.setEmail(email);
                employee.setEmployee_id(empId);
                employee.setSalary(salary);
            }

        } catch (Exception e) {
            e.printStackTrace();
        }

        return employee;
    }

    @Override
    public void updateEmployeeEmailById(String newEmail, Integer employeeId) {

        String SQL = "UPDATE employee_table set email=? WHERE employee_id=?;";
        try(Connection connection = DBUtil.getConnection();PreparedStatement ps = connection.prepareStatement(SQL)) {

            ps.setString(1, newEmail);
            ps.setInt(2,employeeId);

            int executeUpdate = ps.executeUpdate();

            if(executeUpdate ==1){
                System.out.println("Employee email is updated..");
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    @Override
    public void deleteEmployeeById(Integer employeeId) {

        String SQL = "DELETE FROM employee_table WHERE employee_id=?;";
        try(Connection connection = DBUtil.getConnection();PreparedStatement ps = connection.prepareStatement(SQL)) {

            ps.setInt(1,employeeId);

            int executeUpdate = ps.executeUpdate();

            if(executeUpdate ==1){
                System.out.println("Employee is deleted with ID::"+employeeId);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }

    }

    @Override
    public List<Employee> getAllEmployeesInfo() {

        List<Employee> empList = new ArrayList<>();
        String SQL = "SELECT *FROM employee_table";
        try(Connection connection = DBUtil.getConnection();PreparedStatement ps = connection.prepareStatement(SQL)) {

            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                int empId = rs.getInt("employee_id");
                String eName = rs.getString("employee_name");
                String email = rs.getString("email");
                Double salary = rs.getDouble("salary");
                BigDecimal bonus = rs.getBigDecimal("bonus");
                Date date = rs.getDate("date_of_joining");

                Employee employee = new Employee();
                employee.setEmployeeName(eName);
                employee.setBonus(bonus);
                employee.setDoj(date);
                employee.setEmail(email);
                employee.setEmployee_id(empId);
                employee.setSalary(salary);

                empList.add(employee);
            }

        } catch (Exception e) {
            e.printStackTrace();
        }
        return empList;
    }
}

4.6创建JDBCTest测试上述代码

package com.demos;

import java.math.BigDecimal;
import java.util.Date;
import java.util.List;

import com.demos.dao.EmployeeDAO;
import com.demos.dao.impl.EmployeeDAOImpl;
import com.demos.model.Employee;

public class JDBCTest {

    public static void main(String[] args) {
        try {
            EmployeeDAO employeeDAO = new EmployeeDAOImpl();
            Employee employee = getEmployee();
            employeeDAO.createEmployee(employee);
            //getEmployeeById(employeeDAO);
            //employeeDAO.updateEmployeeEmailById("sam.cs2014@gmail.com", 4);
            //employeeDAO.deleteEmployeeById(6);
            //getAllEmployeesInfo(employeeDAO);
        }
        catch (Exception ex) {
            String msg=ex.toString();
            String error=ex.toString();
        }
    }

    private static void getAllEmployeesInfo(EmployeeDAO employeeDAO) {
        List<Employee> empList = employeeDAO.getAllEmployeesInfo();
        for (Employee employee : empList) {
            System.out.println(employee);
        }
    }

    private static void getEmployeeById(EmployeeDAO employeeDAO) {
        Employee employee2 = employeeDAO.getEmployeeById(4);
        if (employee2 != null) {
            System.out.println(employee2);
        } else {
            System.out.println("Employee does not exist..");
        }
    }

    private static Employee getEmployee() {
        Employee employee = new Employee();
        employee.setBonus(new BigDecimal(290));
        employee.setDoj(new Date());
        employee.setEmployeeName("John");
        employee.setEmail("jonh.cs2016@yahoo.com");
        employee.setSalary(90000.00);
        return employee;
    }
}

回到顶部

5测试程序

在JDBCTest代码块中右击，选择Debug 'JDBCTest.main()'，开始调试程序

1使用IDEA创建maven工程

1.1打开idea，在首页选择Create New Project

1.2选择创建maven项目，设置SDK、勾选Create from archetype，点击next下一步

1.3设置项目路径、项目名称、包名等

1.4点击file->settings，设置maven home路径

1.5点击file->settings，设置Java Compiler版本

1.6点击file->Project Structures，设置jdk版本

回到顶部

2引入mysql依赖包

2.1在pom.xml中添加mysql依赖

<dependencies>
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>5.1.47</version>
        <scope>runtime</scope>
    </dependency>
</dependencies>

2.2在maven加载包

点击maven左上角的Reimport All Maven Projects 加载pox.xml中的jar包，之后Dependencies中会出现依赖的jar包

回到顶部

3准备数据

CREATE TABLE `employee_table` (
  `employee_id` int(11) NOT NULL auto_increment,
  `employee_name` varchar(60) NOT NULL,
  `email` varchar(45) NOT NULL,
  `salary` double default NULL,
  `date_of_joining` datetime default NULL,
  `bonus` decimal(10,0) default NULL,
  PRIMARY KEY  (`employee_id`)
) ;

回到顶部

4编写代码

4.1创建如下包名

com.demos、com.demos.util、com.demos.model、com.demos.dao、com.demos.dao.impl

4.2创建DBUtil工具类

package com.demos.util;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

public class DBUtil {
    private static final String DB_DRIVER_CLASS="com.mysql.jdbc.Driver";
    private static final String DB_USERNAME="root";
    private static final String DB_PASSWORD="123456";
    private static final String DB_URL ="jdbc:mysql://localhost:3306/demos";

    private static Connection connection = null;
    static{
        try {
            // 加载数据库驱动
            Class.forName(DB_DRIVER_CLASS);
            // 获取数据库连接
            connection = DriverManager.getConnection(DB_URL, DB_USERNAME, DB_PASSWORD);
        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        }
    }

    public static Connection getConnection(){
        return connection;
    }
}

4.3创建Employee实体类

package com.demos.model;

import java.math.BigDecimal;
import java.util.Date;

public class Employee {

    private int employee_id;
    private String employeeName;
    private String email;
    private Double salary;
    private Date doj;
    private BigDecimal bonus;

    public int getEmployee_id() {
        return employee_id;
    }
    public void setEmployee_id(int employee_id) {
        this.employee_id = employee_id;
    }
    public String getEmployeeName() {
        return employeeName;
    }
    public void setEmployeeName(String employeeName) {
        this.employeeName = employeeName;
    }
    public String getEmail() {
        return email;
    }
    public void setEmail(String email) {
        this.email = email;
    }
    public Double getSalary() {
        return salary;
    }
    public void setSalary(Double salary) {
        this.salary = salary;
    }
    public Date getDoj() {
        return doj;
    }
    public void setDoj(Date doj) {
        this.doj = doj;
    }
    public BigDecimal getBonus() {
        return bonus;
    }
    public void setBonus(BigDecimal bonus) {
        this.bonus = bonus;
    }
    @Override
    public String toString() {
        return "Employee [employee_id=" + employee_id + ", employeeName=" + employeeName + ", email=" + email
                + ", salary=" + salary + ", doj=" + doj + ", bonus=" + bonus + "]";
    }
}

4.4创建EmployeeDAO接口

package com.demos.dao;

import java.util.List;
import com.demos.model.Employee;
public interface EmployeeDAO {

    public abstract void createEmployee(Employee employee);
    public abstract Employee getEmployeeById(Integer employeeId);
    public abstract void updateEmployeeEmailById(String newEmail,Integer employeeId);
    public abstract void deleteEmployeeById(Integer employeeId);
    public abstract List<Employee> getAllEmployeesInfo();
}

4.5创建EmployeeDAOImpl实现EmployeeDAO接口

package com.demos.dao.impl;

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import com.demos.dao.EmployeeDAO;
import com.demos.model.Employee;
import com.demos.util.DBUtil;
import com.demos.dao.EmployeeDAO;
import com.demos.model.Employee;
import com.demos.util.DBUtil;

public class EmployeeDAOImpl implements EmployeeDAO {

    @Override
    public void createEmployee(Employee employee) {

        String SQL = "INSERT INTO employee_table(employee_name,email,salary,date_of_joining,bonus)VALUES(?,?,?,?,?)";
        try(Connection connection = DBUtil.getConnection(); PreparedStatement ps = connection.prepareStatement(SQL)) {

            ps.setString(1, employee.getEmployeeName());
            ps.setString(2, employee.getEmail());
            ps.setDouble(3, employee.getSalary());
            ps.setDate(4, new Date(employee.getDoj().getTime()));
            ps.setBigDecimal(5, employee.getBonus());

            int executeUpdate = ps.executeUpdate();

            if(executeUpdate ==1){
                System.out.println("Employee is crated..");
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    @Override
    public Employee getEmployeeById(Integer employeeId) {
        Employee employee = null;
        String SQL = "SELECT *FROM employee_table WHERE employee_id=?";
        try(Connection connection = DBUtil.getConnection();PreparedStatement ps = connection.prepareStatement(SQL)) {

            ps.setInt(1, employeeId);

            ResultSet rs = ps.executeQuery();
            if (rs.next()) {
                int empId = rs.getInt("employee_id");
                String eName = rs.getString("employee_name");
                String email = rs.getString("email");
                Double salary = rs.getDouble("salary");
                BigDecimal bonus = rs.getBigDecimal("bonus");
                Date date = rs.getDate("date_of_joining");

                employee = new Employee();
                employee.setEmployeeName(eName);
                employee.setBonus(bonus);
                employee.setDoj(date);
                employee.setEmail(email);
                employee.setEmployee_id(empId);
                employee.setSalary(salary);
            }

        } catch (Exception e) {
            e.printStackTrace();
        }

        return employee;
    }

    @Override
    public void updateEmployeeEmailById(String newEmail, Integer employeeId) {

        String SQL = "UPDATE employee_table set email=? WHERE employee_id=?;";
        try(Connection connection = DBUtil.getConnection();PreparedStatement ps = connection.prepareStatement(SQL)) {

            ps.setString(1, newEmail);
            ps.setInt(2,employeeId);

            int executeUpdate = ps.executeUpdate();

            if(executeUpdate ==1){
                System.out.println("Employee email is updated..");
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    @Override
    public void deleteEmployeeById(Integer employeeId) {

        String SQL = "DELETE FROM employee_table WHERE employee_id=?;";
        try(Connection connection = DBUtil.getConnection();PreparedStatement ps = connection.prepareStatement(SQL)) {

            ps.setInt(1,employeeId);

            int executeUpdate = ps.executeUpdate();

            if(executeUpdate ==1){
                System.out.println("Employee is deleted with ID::"+employeeId);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }

    }

    @Override
    public List<Employee> getAllEmployeesInfo() {

        List<Employee> empList = new ArrayList<>();
        String SQL = "SELECT *FROM employee_table";
        try(Connection connection = DBUtil.getConnection();PreparedStatement ps = connection.prepareStatement(SQL)) {

            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                int empId = rs.getInt("employee_id");
                String eName = rs.getString("employee_name");
                String email = rs.getString("email");
                Double salary = rs.getDouble("salary");
                BigDecimal bonus = rs.getBigDecimal("bonus");
                Date date = rs.getDate("date_of_joining");

                Employee employee = new Employee();
                employee.setEmployeeName(eName);
                employee.setBonus(bonus);
                employee.setDoj(date);
                employee.setEmail(email);
                employee.setEmployee_id(empId);
                employee.setSalary(salary);

                empList.add(employee);
            }

        } catch (Exception e) {
            e.printStackTrace();
        }
        return empList;
    }
}

4.6创建JDBCTest测试上述代码

package com.demos;

import java.math.BigDecimal;
import java.util.Date;
import java.util.List;

import com.demos.dao.EmployeeDAO;
import com.demos.dao.impl.EmployeeDAOImpl;
import com.demos.model.Employee;

public class JDBCTest {

    public static void main(String[] args) {
        try {
            EmployeeDAO employeeDAO = new EmployeeDAOImpl();
            Employee employee = getEmployee();
            employeeDAO.createEmployee(employee);
            //getEmployeeById(employeeDAO);
            //employeeDAO.updateEmployeeEmailById("sam.cs2014@gmail.com", 4);
            //employeeDAO.deleteEmployeeById(6);
            //getAllEmployeesInfo(employeeDAO);
        }
        catch (Exception ex) {
            String msg=ex.toString();
            String error=ex.toString();
        }
    }

    private static void getAllEmployeesInfo(EmployeeDAO employeeDAO) {
        List<Employee> empList = employeeDAO.getAllEmployeesInfo();
        for (Employee employee : empList) {
            System.out.println(employee);
        }
    }

    private static void getEmployeeById(EmployeeDAO employeeDAO) {
        Employee employee2 = employeeDAO.getEmployeeById(4);
        if (employee2 != null) {
            System.out.println(employee2);
        } else {
            System.out.println("Employee does not exist..");
        }
    }

    private static Employee getEmployee() {
        Employee employee = new Employee();
        employee.setBonus(new BigDecimal(290));
        employee.setDoj(new Date());
        employee.setEmployeeName("John");
        employee.setEmail("jonh.cs2016@yahoo.com");
        employee.setSalary(90000.00);
        return employee;
    }
}

回到顶部

5测试程序

在JDBCTest代码块中右击，选择Debug 'JDBCTest.main()'，开始调试程序

1使用IDEA创建maven工程

1.1打开idea，在首页选择Create New Project

1.2选择创建maven项目，设置SDK、勾选Create from archetype，点击next下一步

1.3设置项目路径、项目名称、包名等

1.4点击file->settings，设置maven home路径

1.5点击file->settings，设置Java Compiler版本

1.6点击file->Project Structures，设置jdk版本

回到顶部

2引入mysql依赖包

2.1在pom.xml中添加mysql依赖

<dependencies>
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>5.1.47</version>
        <scope>runtime</scope>
    </dependency>
</dependencies>

2.2在maven加载包

点击maven左上角的Reimport All Maven Projects 加载pox.xml中的jar包，之后Dependencies中会出现依赖的jar包

回到顶部

3准备数据

CREATE TABLE `employee_table` (
  `employee_id` int(11) NOT NULL auto_increment,
  `employee_name` varchar(60) NOT NULL,
  `email` varchar(45) NOT NULL,
  `salary` double default NULL,
  `date_of_joining` datetime default NULL,
  `bonus` decimal(10,0) default NULL,
  PRIMARY KEY  (`employee_id`)
) ;

回到顶部

4编写代码

4.1创建如下包名

com.demos、com.demos.util、com.demos.model、com.demos.dao、com.demos.dao.impl

4.2创建DBUtil工具类

package com.demos.util;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

public class DBUtil {
    private static final String DB_DRIVER_CLASS="com.mysql.jdbc.Driver";
    private static final String DB_USERNAME="root";
    private static final String DB_PASSWORD="123456";
    private static final String DB_URL ="jdbc:mysql://localhost:3306/demos";

    private static Connection connection = null;
    static{
        try {
            // 加载数据库驱动
            Class.forName(DB_DRIVER_CLASS);
            // 获取数据库连接
            connection = DriverManager.getConnection(DB_URL, DB_USERNAME, DB_PASSWORD);
        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        }
    }

    public static Connection getConnection(){
        return connection;
    }
}

4.3创建Employee实体类

package com.demos.model;

import java.math.BigDecimal;
import java.util.Date;

public class Employee {

    private int employee_id;
    private String employeeName;
    private String email;
    private Double salary;
    private Date doj;
    private BigDecimal bonus;

    public int getEmployee_id() {
        return employee_id;
    }
    public void setEmployee_id(int employee_id) {
        this.employee_id = employee_id;
    }
    public String getEmployeeName() {
        return employeeName;
    }
    public void setEmployeeName(String employeeName) {
        this.employeeName = employeeName;
    }
    public String getEmail() {
        return email;
    }
    public void setEmail(String email) {
        this.email = email;
    }
    public Double getSalary() {
        return salary;
    }
    public void setSalary(Double salary) {
        this.salary = salary;
    }
    public Date getDoj() {
        return doj;
    }
    public void setDoj(Date doj) {
        this.doj = doj;
    }
    public BigDecimal getBonus() {
        return bonus;
    }
    public void setBonus(BigDecimal bonus) {
        this.bonus = bonus;
    }
    @Override
    public String toString() {
        return "Employee [employee_id=" + employee_id + ", employeeName=" + employeeName + ", email=" + email
                + ", salary=" + salary + ", doj=" + doj + ", bonus=" + bonus + "]";
    }
}

4.4创建EmployeeDAO接口

package com.demos.dao;

import java.util.List;
import com.demos.model.Employee;
public interface EmployeeDAO {

    public abstract void createEmployee(Employee employee);
    public abstract Employee getEmployeeById(Integer employeeId);
    public abstract void updateEmployeeEmailById(String newEmail,Integer employeeId);
    public abstract void deleteEmployeeById(Integer employeeId);
    public abstract List<Employee> getAllEmployeesInfo();
}

4.5创建EmployeeDAOImpl实现EmployeeDAO接口

package com.demos.dao.impl;

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import com.demos.dao.EmployeeDAO;
import com.demos.model.Employee;
import com.demos.util.DBUtil;
import com.demos.dao.EmployeeDAO;
import com.demos.model.Employee;
import com.demos.util.DBUtil;

public class EmployeeDAOImpl implements EmployeeDAO {

    @Override
    public void createEmployee(Employee employee) {

        String SQL = "INSERT INTO employee_table(employee_name,email,salary,date_of_joining,bonus)VALUES(?,?,?,?,?)";
        try(Connection connection = DBUtil.getConnection(); PreparedStatement ps = connection.prepareStatement(SQL)) {

            ps.setString(1, employee.getEmployeeName());
            ps.setString(2, employee.getEmail());
            ps.setDouble(3, employee.getSalary());
            ps.setDate(4, new Date(employee.getDoj().getTime()));
            ps.setBigDecimal(5, employee.getBonus());

            int executeUpdate = ps.executeUpdate();

            if(executeUpdate ==1){
                System.out.println("Employee is crated..");
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    @Override
    public Employee getEmployeeById(Integer employeeId) {
        Employee employee = null;
        String SQL = "SELECT *FROM employee_table WHERE employee_id=?";
        try(Connection connection = DBUtil.getConnection();PreparedStatement ps = connection.prepareStatement(SQL)) {

            ps.setInt(1, employeeId);

            ResultSet rs = ps.executeQuery();
            if (rs.next()) {
                int empId = rs.getInt("employee_id");
                String eName = rs.getString("employee_name");
                String email = rs.getString("email");
                Double salary = rs.getDouble("salary");
                BigDecimal bonus = rs.getBigDecimal("bonus");
                Date date = rs.getDate("date_of_joining");

                employee = new Employee();
                employee.setEmployeeName(eName);
                employee.setBonus(bonus);
                employee.setDoj(date);
                employee.setEmail(email);
                employee.setEmployee_id(empId);
                employee.setSalary(salary);
            }

        } catch (Exception e) {
            e.printStackTrace();
        }

        return employee;
    }

    @Override
    public void updateEmployeeEmailById(String newEmail, Integer employeeId) {

        String SQL = "UPDATE employee_table set email=? WHERE employee_id=?;";
        try(Connection connection = DBUtil.getConnection();PreparedStatement ps = connection.prepareStatement(SQL)) {

            ps.setString(1, newEmail);
            ps.setInt(2,employeeId);

            int executeUpdate = ps.executeUpdate();

            if(executeUpdate ==1){
                System.out.println("Employee email is updated..");
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    @Override
    public void deleteEmployeeById(Integer employeeId) {

        String SQL = "DELETE FROM employee_table WHERE employee_id=?;";
        try(Connection connection = DBUtil.getConnection();PreparedStatement ps = connection.prepareStatement(SQL)) {

            ps.setInt(1,employeeId);

            int executeUpdate = ps.executeUpdate();

            if(executeUpdate ==1){
                System.out.println("Employee is deleted with ID::"+employeeId);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }

    }

    @Override
    public List<Employee> getAllEmployeesInfo() {

        List<Employee> empList = new ArrayList<>();
        String SQL = "SELECT *FROM employee_table";
        try(Connection connection = DBUtil.getConnection();PreparedStatement ps = connection.prepareStatement(SQL)) {

            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                int empId = rs.getInt("employee_id");
                String eName = rs.getString("employee_name");
                String email = rs.getString("email");
                Double salary = rs.getDouble("salary");
                BigDecimal bonus = rs.getBigDecimal("bonus");
                Date date = rs.getDate("date_of_joining");

                Employee employee = new Employee();
                employee.setEmployeeName(eName);
                employee.setBonus(bonus);
                employee.setDoj(date);
                employee.setEmail(email);
                employee.setEmployee_id(empId);
                employee.setSalary(salary);

                empList.add(employee);
            }

        } catch (Exception e) {
            e.printStackTrace();
        }
        return empList;
    }
}

4.6创建JDBCTest测试上述代码

package com.demos;

import java.math.BigDecimal;
import java.util.Date;
import java.util.List;

import com.demos.dao.EmployeeDAO;
import com.demos.dao.impl.EmployeeDAOImpl;
import com.demos.model.Employee;

public class JDBCTest {

    public static void main(String[] args) {
        try {
            EmployeeDAO employeeDAO = new EmployeeDAOImpl();
            Employee employee = getEmployee();
            employeeDAO.createEmployee(employee);
            //getEmployeeById(employeeDAO);
            //employeeDAO.updateEmployeeEmailById("sam.cs2014@gmail.com", 4);
            //employeeDAO.deleteEmployeeById(6);
            //getAllEmployeesInfo(employeeDAO);
        }
        catch (Exception ex) {
            String msg=ex.toString();
            String error=ex.toString();
        }
    }

    private static void getAllEmployeesInfo(EmployeeDAO employeeDAO) {
        List<Employee> empList = employeeDAO.getAllEmployeesInfo();
        for (Employee employee : empList) {
            System.out.println(employee);
        }
    }

    private static void getEmployeeById(EmployeeDAO employeeDAO) {
        Employee employee2 = employeeDAO.getEmployeeById(4);
        if (employee2 != null) {
            System.out.println(employee2);
        } else {
            System.out.println("Employee does not exist..");
        }
    }

    private static Employee getEmployee() {
        Employee employee = new Employee();
        employee.setBonus(new BigDecimal(290));
        employee.setDoj(new Date());
        employee.setEmployeeName("John");
        employee.setEmail("jonh.cs2016@yahoo.com");
        employee.setSalary(90000.00);
        return employee;
    }
}

回到顶部

5测试程序

在JDBCTest代码块中右击，选择Debug 'JDBCTest.main()'，开始调试程序

1使用IDEA创建maven工程

1.1打开idea，在首页选择Create New Project

1.2选择创建maven项目，设置SDK、勾选Create from archetype，点击next下一步

1.3设置项目路径、项目名称、包名等

1.4点击file->settings，设置maven home路径

1.5点击file->settings，设置Java Compiler版本

1.6点击file->Project Structures，设置jdk版本

回到顶部

2引入mysql依赖包

2.1在pom.xml中添加mysql依赖

<dependencies>
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>5.1.47</version>
        <scope>runtime</scope>
    </dependency>
</dependencies>

2.2在maven加载包

点击maven左上角的Reimport All Maven Projects 加载pox.xml中的jar包，之后Dependencies中会出现依赖的jar包

回到顶部

3准备数据

CREATE TABLE `employee_table` (
  `employee_id` int(11) NOT NULL auto_increment,
  `employee_name` varchar(60) NOT NULL,
  `email` varchar(45) NOT NULL,
  `salary` double default NULL,
  `date_of_joining` datetime default NULL,
  `bonus` decimal(10,0) default NULL,
  PRIMARY KEY  (`employee_id`)
) ;

回到顶部

4编写代码

4.1创建如下包名

com.demos、com.demos.util、com.demos.model、com.demos.dao、com.demos.dao.impl

4.2创建DBUtil工具类

package com.demos.util;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

public class DBUtil {
    private static final String DB_DRIVER_CLASS="com.mysql.jdbc.Driver";
    private static final String DB_USERNAME="root";
    private static final String DB_PASSWORD="123456";
    private static final String DB_URL ="jdbc:mysql://localhost:3306/demos";

    private static Connection connection = null;
    static{
        try {
            // 加载数据库驱动
            Class.forName(DB_DRIVER_CLASS);
            // 获取数据库连接
            connection = DriverManager.getConnection(DB_URL, DB_USERNAME, DB_PASSWORD);
        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        }
    }

    public static Connection getConnection(){
        return connection;
    }
}

4.3创建Employee实体类

package com.demos.model;

import java.math.BigDecimal;
import java.util.Date;

public class Employee {

    private int employee_id;
    private String employeeName;
    private String email;
    private Double salary;
    private Date doj;
    private BigDecimal bonus;

    public int getEmployee_id() {
        return employee_id;
    }
    public void setEmployee_id(int employee_id) {
        this.employee_id = employee_id;
    }
    public String getEmployeeName() {
        return employeeName;
    }
    public void setEmployeeName(String employeeName) {
        this.employeeName = employeeName;
    }
    public String getEmail() {
        return email;
    }
    public void setEmail(String email) {
        this.email = email;
    }
    public Double getSalary() {
        return salary;
    }
    public void setSalary(Double salary) {
        this.salary = salary;
    }
    public Date getDoj() {
        return doj;
    }
    public void setDoj(Date doj) {
        this.doj = doj;
    }
    public BigDecimal getBonus() {
        return bonus;
    }
    public void setBonus(BigDecimal bonus) {
        this.bonus = bonus;
    }
    @Override
    public String toString() {
        return "Employee [employee_id=" + employee_id + ", employeeName=" + employeeName + ", email=" + email
                + ", salary=" + salary + ", doj=" + doj + ", bonus=" + bonus + "]";
    }
}

4.4创建EmployeeDAO接口

package com.demos.dao;

import java.util.List;
import com.demos.model.Employee;
public interface EmployeeDAO {

    public abstract void createEmployee(Employee employee);
    public abstract Employee getEmployeeById(Integer employeeId);
    public abstract void updateEmployeeEmailById(String newEmail,Integer employeeId);
    public abstract void deleteEmployeeById(Integer employeeId);
    public abstract List<Employee> getAllEmployeesInfo();
}

4.5创建EmployeeDAOImpl实现EmployeeDAO接口

package com.demos.dao.impl;

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import com.demos.dao.EmployeeDAO;
import com.demos.model.Employee;
import com.demos.util.DBUtil;
import com.demos.dao.EmployeeDAO;
import com.demos.model.Employee;
import com.demos.util.DBUtil;

public class EmployeeDAOImpl implements EmployeeDAO {

    @Override
    public void createEmployee(Employee employee) {

        String SQL = "INSERT INTO employee_table(employee_name,email,salary,date_of_joining,bonus)VALUES(?,?,?,?,?)";
        try(Connection connection = DBUtil.getConnection(); PreparedStatement ps = connection.prepareStatement(SQL)) {

            ps.setString(1, employee.getEmployeeName());
            ps.setString(2, employee.getEmail());
            ps.setDouble(3, employee.getSalary());
            ps.setDate(4, new Date(employee.getDoj().getTime()));
            ps.setBigDecimal(5, employee.getBonus());

            int executeUpdate = ps.executeUpdate();

            if(executeUpdate ==1){
                System.out.println("Employee is crated..");
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    @Override
    public Employee getEmployeeById(Integer employeeId) {
        Employee employee = null;
        String SQL = "SELECT *FROM employee_table WHERE employee_id=?";
        try(Connection connection = DBUtil.getConnection();PreparedStatement ps = connection.prepareStatement(SQL)) {

            ps.setInt(1, employeeId);

            ResultSet rs = ps.executeQuery();
            if (rs.next()) {
                int empId = rs.getInt("employee_id");
                String eName = rs.getString("employee_name");
                String email = rs.getString("email");
                Double salary = rs.getDouble("salary");
                BigDecimal bonus = rs.getBigDecimal("bonus");
                Date date = rs.getDate("date_of_joining");

                employee = new Employee();
                employee.setEmployeeName(eName);
                employee.setBonus(bonus);
                employee.setDoj(date);
                employee.setEmail(email);
                employee.setEmployee_id(empId);
                employee.setSalary(salary);
            }

        } catch (Exception e) {
            e.printStackTrace();
        }

        return employee;
    }

    @Override
    public void updateEmployeeEmailById(String newEmail, Integer employeeId) {

        String SQL = "UPDATE employee_table set email=? WHERE employee_id=?;";
        try(Connection connection = DBUtil.getConnection();PreparedStatement ps = connection.prepareStatement(SQL)) {

            ps.setString(1, newEmail);
            ps.setInt(2,employeeId);

            int executeUpdate = ps.executeUpdate();

            if(executeUpdate ==1){
                System.out.println("Employee email is updated..");
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    @Override
    public void deleteEmployeeById(Integer employeeId) {

        String SQL = "DELETE FROM employee_table WHERE employee_id=?;";
        try(Connection connection = DBUtil.getConnection();PreparedStatement ps = connection.prepareStatement(SQL)) {

            ps.setInt(1,employeeId);

            int executeUpdate = ps.executeUpdate();

            if(executeUpdate ==1){
                System.out.println("Employee is deleted with ID::"+employeeId);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }

    }

    @Override
    public List<Employee> getAllEmployeesInfo() {

        List<Employee> empList = new ArrayList<>();
        String SQL = "SELECT *FROM employee_table";
        try(Connection connection = DBUtil.getConnection();PreparedStatement ps = connection.prepareStatement(SQL)) {

            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                int empId = rs.getInt("employee_id");
                String eName = rs.getString("employee_name");
                String email = rs.getString("email");
                Double salary = rs.getDouble("salary");
                BigDecimal bonus = rs.getBigDecimal("bonus");
                Date date = rs.getDate("date_of_joining");

                Employee employee = new Employee();
                employee.setEmployeeName(eName);
                employee.setBonus(bonus);
                employee.setDoj(date);
                employee.setEmail(email);
                employee.setEmployee_id(empId);
                employee.setSalary(salary);

                empList.add(employee);
            }

        } catch (Exception e) {
            e.printStackTrace();
        }
        return empList;
    }
}

4.6创建JDBCTest测试上述代码

package com.demos;

import java.math.BigDecimal;
import java.util.Date;
import java.util.List;

import com.demos.dao.EmployeeDAO;
import com.demos.dao.impl.EmployeeDAOImpl;
import com.demos.model.Employee;

public class JDBCTest {

    public static void main(String[] args) {
        try {
            EmployeeDAO employeeDAO = new EmployeeDAOImpl();
            Employee employee = getEmployee();
            employeeDAO.createEmployee(employee);
            //getEmployeeById(employeeDAO);
            //employeeDAO.updateEmployeeEmailById("sam.cs2014@gmail.com", 4);
            //employeeDAO.deleteEmployeeById(6);
            //getAllEmployeesInfo(employeeDAO);
        }
        catch (Exception ex) {
            String msg=ex.toString();
            String error=ex.toString();
        }
    }

    private static void getAllEmployeesInfo(EmployeeDAO employeeDAO) {
        List<Employee> empList = employeeDAO.getAllEmployeesInfo();
        for (Employee employee : empList) {
            System.out.println(employee);
        }
    }

    private static void getEmployeeById(EmployeeDAO employeeDAO) {
        Employee employee2 = employeeDAO.getEmployeeById(4);
        if (employee2 != null) {
            System.out.println(employee2);
        } else {
            System.out.println("Employee does not exist..");
        }
    }

    private static Employee getEmployee() {
        Employee employee = new Employee();
        employee.setBonus(new BigDecimal(290));
        employee.setDoj(new Date());
        employee.setEmployeeName("John");
        employee.setEmail("jonh.cs2016@yahoo.com");
        employee.setSalary(90000.00);
        return employee;
    }
}

回到顶部

5测试程序

在JDBCTest代码块中右击，选择Debug 'JDBCTest.main()'，开始调试程序

1使用IDEA创建maven工程

1.1打开idea，在首页选择Create New Project

1.2选择创建maven项目，设置SDK、勾选Create from archetype，点击next下一步

1.3设置项目路径、项目名称、包名等

1.4点击file->settings，设置maven home路径

1.5点击file->settings，设置Java Compiler版本

1.6点击file->Project Structures，设置jdk版本

回到顶部

2引入mysql依赖包

2.1在pom.xml中添加mysql依赖

<dependencies>
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>5.1.47</version>
        <scope>runtime</scope>
    </dependency>
</dependencies>

2.2在maven加载包

点击maven左上角的Reimport All Maven Projects 加载pox.xml中的jar包，之后Dependencies中会出现依赖的jar包

回到顶部

3准备数据

CREATE TABLE `employee_table` (
  `employee_id` int(11) NOT NULL auto_increment,
  `employee_name` varchar(60) NOT NULL,
  `email` varchar(45) NOT NULL,
  `salary` double default NULL,
  `date_of_joining` datetime default NULL,
  `bonus` decimal(10,0) default NULL,
  PRIMARY KEY  (`employee_id`)
) ;

回到顶部

4编写代码

4.1创建如下包名

com.demos、com.demos.util、com.demos.model、com.demos.dao、com.demos.dao.impl

4.2创建DBUtil工具类

package com.demos.util;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

public class DBUtil {
    private static final String DB_DRIVER_CLASS="com.mysql.jdbc.Driver";
    private static final String DB_USERNAME="root";
    private static final String DB_PASSWORD="123456";
    private static final String DB_URL ="jdbc:mysql://localhost:3306/demos";

    private static Connection connection = null;
    static{
        try {
            // 加载数据库驱动
            Class.forName(DB_DRIVER_CLASS);
            // 获取数据库连接
            connection = DriverManager.getConnection(DB_URL, DB_USERNAME, DB_PASSWORD);
        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        }
    }

    public static Connection getConnection(){
        return connection;
    }
}

4.3创建Employee实体类

package com.demos.model;

import java.math.BigDecimal;
import java.util.Date;

public class Employee {

    private int employee_id;
    private String employeeName;
    private String email;
    private Double salary;
    private Date doj;
    private BigDecimal bonus;

    public int getEmployee_id() {
        return employee_id;
    }
    public void setEmployee_id(int employee_id) {
        this.employee_id = employee_id;
    }
    public String getEmployeeName() {
        return employeeName;
    }
    public void setEmployeeName(String employeeName) {
        this.employeeName = employeeName;
    }
    public String getEmail() {
        return email;
    }
    public void setEmail(String email) {
        this.email = email;
    }
    public Double getSalary() {
        return salary;
    }
    public void setSalary(Double salary) {
        this.salary = salary;
    }
    public Date getDoj() {
        return doj;
    }
    public void setDoj(Date doj) {
        this.doj = doj;
    }
    public BigDecimal getBonus() {
        return bonus;
    }
    public void setBonus(BigDecimal bonus) {
        this.bonus = bonus;
    }
    @Override
    public String toString() {
        return "Employee [employee_id=" + employee_id + ", employeeName=" + employeeName + ", email=" + email
                + ", salary=" + salary + ", doj=" + doj + ", bonus=" + bonus + "]";
    }
}

4.4创建EmployeeDAO接口

package com.demos.dao;

import java.util.List;
import com.demos.model.Employee;
public interface EmployeeDAO {

    public abstract void createEmployee(Employee employee);
    public abstract Employee getEmployeeById(Integer employeeId);
    public abstract void updateEmployeeEmailById(String newEmail,Integer employeeId);
    public abstract void deleteEmployeeById(Integer employeeId);
    public abstract List<Employee> getAllEmployeesInfo();
}

4.5创建EmployeeDAOImpl实现EmployeeDAO接口

package com.demos.dao.impl;

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import com.demos.dao.EmployeeDAO;
import com.demos.model.Employee;
import com.demos.util.DBUtil;
import com.demos.dao.EmployeeDAO;
import com.demos.model.Employee;
import com.demos.util.DBUtil;

public class EmployeeDAOImpl implements EmployeeDAO {

    @Override
    public void createEmployee(Employee employee) {

        String SQL = "INSERT INTO employee_table(employee_name,email,salary,date_of_joining,bonus)VALUES(?,?,?,?,?)";
        try(Connection connection = DBUtil.getConnection(); PreparedStatement ps = connection.prepareStatement(SQL)) {

            ps.setString(1, employee.getEmployeeName());
            ps.setString(2, employee.getEmail());
            ps.setDouble(3, employee.getSalary());
            ps.setDate(4, new Date(employee.getDoj().getTime()));
            ps.setBigDecimal(5, employee.getBonus());

            int executeUpdate = ps.executeUpdate();

            if(executeUpdate ==1){
                System.out.println("Employee is crated..");
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    @Override
    public Employee getEmployeeById(Integer employeeId) {
        Employee employee = null;
        String SQL = "SELECT *FROM employee_table WHERE employee_id=?";
        try(Connection connection = DBUtil.getConnection();PreparedStatement ps = connection.prepareStatement(SQL)) {

            ps.setInt(1, employeeId);

            ResultSet rs = ps.executeQuery();
            if (rs.next()) {
                int empId = rs.getInt("employee_id");
                String eName = rs.getString("employee_name");
                String email = rs.getString("email");
                Double salary = rs.getDouble("salary");
                BigDecimal bonus = rs.getBigDecimal("bonus");
                Date date = rs.getDate("date_of_joining");

                employee = new Employee();
                employee.setEmployeeName(eName);
                employee.setBonus(bonus);
                employee.setDoj(date);
                employee.setEmail(email);
                employee.setEmployee_id(empId);
                employee.setSalary(salary);
            }

        } catch (Exception e) {
            e.printStackTrace();
        }

        return employee;
    }

    @Override
    public void updateEmployeeEmailById(String newEmail, Integer employeeId) {

        String SQL = "UPDATE employee_table set email=? WHERE employee_id=?;";
        try(Connection connection = DBUtil.getConnection();PreparedStatement ps = connection.prepareStatement(SQL)) {

            ps.setString(1, newEmail);
            ps.setInt(2,employeeId);

            int executeUpdate = ps.executeUpdate();

            if(executeUpdate ==1){
                System.out.println("Employee email is updated..");
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    @Override
    public void deleteEmployeeById(Integer employeeId) {

        String SQL = "DELETE FROM employee_table WHERE employee_id=?;";
        try(Connection connection = DBUtil.getConnection();PreparedStatement ps = connection.prepareStatement(SQL)) {

            ps.setInt(1,employeeId);

            int executeUpdate = ps.executeUpdate();

            if(executeUpdate ==1){
                System.out.println("Employee is deleted with ID::"+employeeId);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }

    }

    @Override
    public List<Employee> getAllEmployeesInfo() {

        List<Employee> empList = new ArrayList<>();
        String SQL = "SELECT *FROM employee_table";
        try(Connection connection = DBUtil.getConnection();PreparedStatement ps = connection.prepareStatement(SQL)) {

            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                int empId = rs.getInt("employee_id");
                String eName = rs.getString("employee_name");
                String email = rs.getString("email");
                Double salary = rs.getDouble("salary");
                BigDecimal bonus = rs.getBigDecimal("bonus");
                Date date = rs.getDate("date_of_joining");

                Employee employee = new Employee();
                employee.setEmployeeName(eName);
                employee.setBonus(bonus);
                employee.setDoj(date);
                employee.setEmail(email);
                employee.setEmployee_id(empId);
                employee.setSalary(salary);

                empList.add(employee);
            }

        } catch (Exception e) {
            e.printStackTrace();
        }
        return empList;
    }
}

4.6创建JDBCTest测试上述代码

package com.demos;

import java.math.BigDecimal;
import java.util.Date;
import java.util.List;

import com.demos.dao.EmployeeDAO;
import com.demos.dao.impl.EmployeeDAOImpl;
import com.demos.model.Employee;

public class JDBCTest {

    public static void main(String[] args) {
        try {
            EmployeeDAO employeeDAO = new EmployeeDAOImpl();
            Employee employee = getEmployee();
            employeeDAO.createEmployee(employee);
            //getEmployeeById(employeeDAO);
            //employeeDAO.updateEmployeeEmailById("sam.cs2014@gmail.com", 4);
            //employeeDAO.deleteEmployeeById(6);
            //getAllEmployeesInfo(employeeDAO);
        }
        catch (Exception ex) {
            String msg=ex.toString();
            String error=ex.toString();
        }
    }

    private static void getAllEmployeesInfo(EmployeeDAO employeeDAO) {
        List<Employee> empList = employeeDAO.getAllEmployeesInfo();
        for (Employee employee : empList) {
            System.out.println(employee);
        }
    }

    private static void getEmployeeById(EmployeeDAO employeeDAO) {
        Employee employee2 = employeeDAO.getEmployeeById(4);
        if (employee2 != null) {
            System.out.println(employee2);
        } else {
            System.out.println("Employee does not exist..");
        }
    }

    private static Employee getEmployee() {
        Employee employee = new Employee();
        employee.setBonus(new BigDecimal(290));
        employee.setDoj(new Date());
        employee.setEmployeeName("John");
        employee.setEmail("jonh.cs2016@yahoo.com");
        employee.setSalary(90000.00);
        return employee;
    }
}

回到顶部

5测试程序

在JDBCTest代码块中右击，选择Debug 'JDBCTest.main()'，开始调试程序

1使用IDEA创建maven工程

1.1打开idea，在首页选择Create New Project

1.2选择创建maven项目，设置SDK、勾选Create from archetype，点击next下一步

1.3设置项目路径、项目名称、包名等

1.4点击file->settings，设置maven home路径

1.5点击file->settings，设置Java Compiler版本

1.6点击file->Project Structures，设置jdk版本

回到顶部

2引入mysql依赖包

2.1在pom.xml中添加mysql依赖

<dependencies>
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>5.1.47</version>
        <scope>runtime</scope>
    </dependency>
</dependencies>

2.2在maven加载包

点击maven左上角的Reimport All Maven Projects 加载pox.xml中的jar包，之后Dependencies中会出现依赖的jar包

回到顶部

3准备数据

CREATE TABLE `employee_table` (
  `employee_id` int(11) NOT NULL auto_increment,
  `employee_name` varchar(60) NOT NULL,
  `email` varchar(45) NOT NULL,
  `salary` double default NULL,
  `date_of_joining` datetime default NULL,
  `bonus` decimal(10,0) default NULL,
  PRIMARY KEY  (`employee_id`)
) ;

回到顶部

4编写代码

4.1创建如下包名

com.demos、com.demos.util、com.demos.model、com.demos.dao、com.demos.dao.impl

4.2创建DBUtil工具类

package com.demos.util;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

public class DBUtil {
    private static final String DB_DRIVER_CLASS="com.mysql.jdbc.Driver";
    private static final String DB_USERNAME="root";
    private static final String DB_PASSWORD="123456";
    private static final String DB_URL ="jdbc:mysql://localhost:3306/demos";

    private static Connection connection = null;
    static{
        try {
            // 加载数据库驱动
            Class.forName(DB_DRIVER_CLASS);
            // 获取数据库连接
            connection = DriverManager.getConnection(DB_URL, DB_USERNAME, DB_PASSWORD);
        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        }
    }

    public static Connection getConnection(){
        return connection;
    }
}

4.3创建Employee实体类

package com.demos.model;

import java.math.BigDecimal;
import java.util.Date;

public class Employee {

    private int employee_id;
    private String employeeName;
    private String email;
    private Double salary;
    private Date doj;
    private BigDecimal bonus;

    public int getEmployee_id() {
        return employee_id;
    }
    public void setEmployee_id(int employee_id) {
        this.employee_id = employee_id;
    }
    public String getEmployeeName() {
        return employeeName;
    }
    public void setEmployeeName(String employeeName) {
        this.employeeName = employeeName;
    }
    public String getEmail() {
        return email;
    }
    public void setEmail(String email) {
        this.email = email;
    }
    public Double getSalary() {
        return salary;
    }
    public void setSalary(Double salary) {
        this.salary = salary;
    }
    public Date getDoj() {
        return doj;
    }
    public void setDoj(Date doj) {
        this.doj = doj;
    }
    public BigDecimal getBonus() {
        return bonus;
    }
    public void setBonus(BigDecimal bonus) {
        this.bonus = bonus;
    }
    @Override
    public String toString() {
        return "Employee [employee_id=" + employee_id + ", employeeName=" + employeeName + ", email=" + email
                + ", salary=" + salary + ", doj=" + doj + ", bonus=" + bonus + "]";
    }
}

4.4创建EmployeeDAO接口

package com.demos.dao;

import java.util.List;
import com.demos.model.Employee;
public interface EmployeeDAO {

    public abstract void createEmployee(Employee employee);
    public abstract Employee getEmployeeById(Integer employeeId);
    public abstract void updateEmployeeEmailById(String newEmail,Integer employeeId);
    public abstract void deleteEmployeeById(Integer employeeId);
    public abstract List<Employee> getAllEmployeesInfo();
}

4.5创建EmployeeDAOImpl实现EmployeeDAO接口

package com.demos.dao.impl;

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import com.demos.dao.EmployeeDAO;
import com.demos.model.Employee;
import com.demos.util.DBUtil;
import com.demos.dao.EmployeeDAO;
import com.demos.model.Employee;
import com.demos.util.DBUtil;

public class EmployeeDAOImpl implements EmployeeDAO {

    @Override
    public void createEmployee(Employee employee) {

        String SQL = "INSERT INTO employee_table(employee_name,email,salary,date_of_joining,bonus)VALUES(?,?,?,?,?)";
        try(Connection connection = DBUtil.getConnection(); PreparedStatement ps = connection.prepareStatement(SQL)) {

            ps.setString(1, employee.getEmployeeName());
            ps.setString(2, employee.getEmail());
            ps.setDouble(3, employee.getSalary());
            ps.setDate(4, new Date(employee.getDoj().getTime()));
            ps.setBigDecimal(5, employee.getBonus());

            int executeUpdate = ps.executeUpdate();

            if(executeUpdate ==1){
                System.out.println("Employee is crated..");
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    @Override
    public Employee getEmployeeById(Integer employeeId) {
        Employee employee = null;
        String SQL = "SELECT *FROM employee_table WHERE employee_id=?";
        try(Connection connection = DBUtil.getConnection();PreparedStatement ps = connection.prepareStatement(SQL)) {

            ps.setInt(1, employeeId);

            ResultSet rs = ps.executeQuery();
            if (rs.next()) {
                int empId = rs.getInt("employee_id");
                String eName = rs.getString("employee_name");
                String email = rs.getString("email");
                Double salary = rs.getDouble("salary");
                BigDecimal bonus = rs.getBigDecimal("bonus");
                Date date = rs.getDate("date_of_joining");

                employee = new Employee();
                employee.setEmployeeName(eName);
                employee.setBonus(bonus);
                employee.setDoj(date);
                employee.setEmail(email);
                employee.setEmployee_id(empId);
                employee.setSalary(salary);
            }

        } catch (Exception e) {
            e.printStackTrace();
        }

        return employee;
    }

    @Override
    public void updateEmployeeEmailById(String newEmail, Integer employeeId) {

        String SQL = "UPDATE employee_table set email=? WHERE employee_id=?;";
        try(Connection connection = DBUtil.getConnection();PreparedStatement ps = connection.prepareStatement(SQL)) {

            ps.setString(1, newEmail);
            ps.setInt(2,employeeId);

            int executeUpdate = ps.executeUpdate();

            if(executeUpdate ==1){
                System.out.println("Employee email is updated..");
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    @Override
    public void deleteEmployeeById(Integer employeeId) {

        String SQL = "DELETE FROM employee_table WHERE employee_id=?;";
        try(Connection connection = DBUtil.getConnection();PreparedStatement ps = connection.prepareStatement(SQL)) {

            ps.setInt(1,employeeId);

            int executeUpdate = ps.executeUpdate();

            if(executeUpdate ==1){
                System.out.println("Employee is deleted with ID::"+employeeId);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }

    }

    @Override
    public List<Employee> getAllEmployeesInfo() {

        List<Employee> empList = new ArrayList<>();
        String SQL = "SELECT *FROM employee_table";
        try(Connection connection = DBUtil.getConnection();PreparedStatement ps = connection.prepareStatement(SQL)) {

            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                int empId = rs.getInt("employee_id");
                String eName = rs.getString("employee_name");
                String email = rs.getString("email");
                Double salary = rs.getDouble("salary");
                BigDecimal bonus = rs.getBigDecimal("bonus");
                Date date = rs.getDate("date_of_joining");

                Employee employee = new Employee();
                employee.setEmployeeName(eName);
                employee.setBonus(bonus);
                employee.setDoj(date);
                employee.setEmail(email);
                employee.setEmployee_id(empId);
                employee.setSalary(salary);

                empList.add(employee);
            }

        } catch (Exception e) {
            e.printStackTrace();
        }
        return empList;
    }
}

4.6创建JDBCTest测试上述代码

package com.demos;

import java.math.BigDecimal;
import java.util.Date;
import java.util.List;

import com.demos.dao.EmployeeDAO;
import com.demos.dao.impl.EmployeeDAOImpl;
import com.demos.model.Employee;

public class JDBCTest {

    public static void main(String[] args) {
        try {
            EmployeeDAO employeeDAO = new EmployeeDAOImpl();
            Employee employee = getEmployee();
            employeeDAO.createEmployee(employee);
            //getEmployeeById(employeeDAO);
            //employeeDAO.updateEmployeeEmailById("sam.cs2014@gmail.com", 4);
            //employeeDAO.deleteEmployeeById(6);
            //getAllEmployeesInfo(employeeDAO);
        }
        catch (Exception ex) {
            String msg=ex.toString();
            String error=ex.toString();
        }
    }

    private static void getAllEmployeesInfo(EmployeeDAO employeeDAO) {
        List<Employee> empList = employeeDAO.getAllEmployeesInfo();
        for (Employee employee : empList) {
            System.out.println(employee);
        }
    }

    private static void getEmployeeById(EmployeeDAO employeeDAO) {
        Employee employee2 = employeeDAO.getEmployeeById(4);
        if (employee2 != null) {
            System.out.println(employee2);
        } else {
            System.out.println("Employee does not exist..");
        }
    }

    private static Employee getEmployee() {
        Employee employee = new Employee();
        employee.setBonus(new BigDecimal(290));
        employee.setDoj(new Date());
        employee.setEmployeeName("John");
        employee.setEmail("jonh.cs2016@yahoo.com");
        employee.setSalary(90000.00);
        return employee;
    }
}

回到顶部

5测试程序

在JDBCTest代码块中右击，选择Debug 'JDBCTest.main()'，开始调试程序

1使用IDEA创建maven工程

1.1打开idea，在首页选择Create New Project

1.2选择创建maven项目，设置SDK、勾选Create from archetype，点击next下一步

1.3设置项目路径、项目名称、包名等

1.4点击file->settings，设置maven home路径

1.5点击file->settings，设置Java Compiler版本

1.6点击file->Project Structures，设置jdk版本

回到顶部

2引入mysql依赖包

2.1在pom.xml中添加mysql依赖

<dependencies>
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>5.1.47</version>
        <scope>runtime</scope>
    </dependency>
</dependencies>

2.2在maven加载包

点击maven左上角的Reimport All Maven Projects 加载pox.xml中的jar包，之后Dependencies中会出现依赖的jar包

回到顶部

3准备数据

CREATE TABLE `employee_table` (
  `employee_id` int(11) NOT NULL auto_increment,
  `employee_name` varchar(60) NOT NULL,
  `email` varchar(45) NOT NULL,
  `salary` double default NULL,
  `date_of_joining` datetime default NULL,
  `bonus` decimal(10,0) default NULL,
  PRIMARY KEY  (`employee_id`)
) ;

回到顶部

4编写代码

4.1创建如下包名

com.demos、com.demos.util、com.demos.model、com.demos.dao、com.demos.dao.impl

4.2创建DBUtil工具类

package com.demos.util;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

public class DBUtil {
    private static final String DB_DRIVER_CLASS="com.mysql.jdbc.Driver";
    private static final String DB_USERNAME="root";
    private static final String DB_PASSWORD="123456";
    private static final String DB_URL ="jdbc:mysql://localhost:3306/demos";

    private static Connection connection = null;
    static{
        try {
            // 加载数据库驱动
            Class.forName(DB_DRIVER_CLASS);
            // 获取数据库连接
            connection = DriverManager.getConnection(DB_URL, DB_USERNAME, DB_PASSWORD);
        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        }
    }

    public static Connection getConnection(){
        return connection;
    }
}

4.3创建Employee实体类

package com.demos.model;

import java.math.BigDecimal;
import java.util.Date;

public class Employee {

    private int employee_id;
    private String employeeName;
    private String email;
    private Double salary;
    private Date doj;
    private BigDecimal bonus;

    public int getEmployee_id() {
        return employee_id;
    }
    public void setEmployee_id(int employee_id) {
        this.employee_id = employee_id;
    }
    public String getEmployeeName() {
        return employeeName;
    }
    public void setEmployeeName(String employeeName) {
        this.employeeName = employeeName;
    }
    public String getEmail() {
        return email;
    }
    public void setEmail(String email) {
        this.email = email;
    }
    public Double getSalary() {
        return salary;
    }
    public void setSalary(Double salary) {
        this.salary = salary;
    }
    public Date getDoj() {
        return doj;
    }
    public void setDoj(Date doj) {
        this.doj = doj;
    }
    public BigDecimal getBonus() {
        return bonus;
    }
    public void setBonus(BigDecimal bonus) {
        this.bonus = bonus;
    }
    @Override
    public String toString() {
        return "Employee [employee_id=" + employee_id + ", employeeName=" + employeeName + ", email=" + email
                + ", salary=" + salary + ", doj=" + doj + ", bonus=" + bonus + "]";
    }
}

4.4创建EmployeeDAO接口

package com.demos.dao;

import java.util.List;
import com.demos.model.Employee;
public interface EmployeeDAO {

    public abstract void createEmployee(Employee employee);
    public abstract Employee getEmployeeById(Integer employeeId);
    public abstract void updateEmployeeEmailById(String newEmail,Integer employeeId);
    public abstract void deleteEmployeeById(Integer employeeId);
    public abstract List<Employee> getAllEmployeesInfo();
}

4.5创建EmployeeDAOImpl实现EmployeeDAO接口

package com.demos.dao.impl;

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import com.demos.dao.EmployeeDAO;
import com.demos.model.Employee;
import com.demos.util.DBUtil;
import com.demos.dao.EmployeeDAO;
import com.demos.model.Employee;
import com.demos.util.DBUtil;

public class EmployeeDAOImpl implements EmployeeDAO {

    @Override
    public void createEmployee(Employee employee) {

        String SQL = "INSERT INTO employee_table(employee_name,email,salary,date_of_joining,bonus)VALUES(?,?,?,?,?)";
        try(Connection connection = DBUtil.getConnection(); PreparedStatement ps = connection.prepareStatement(SQL)) {

            ps.setString(1, employee.getEmployeeName());
            ps.setString(2, employee.getEmail());
            ps.setDouble(3, employee.getSalary());
            ps.setDate(4, new Date(employee.getDoj().getTime()));
            ps.setBigDecimal(5, employee.getBonus());

            int executeUpdate = ps.executeUpdate();

            if(executeUpdate ==1){
                System.out.println("Employee is crated..");
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    @Override
    public Employee getEmployeeById(Integer employeeId) {
        Employee employee = null;
        String SQL = "SELECT *FROM employee_table WHERE employee_id=?";
        try(Connection connection = DBUtil.getConnection();PreparedStatement ps = connection.prepareStatement(SQL)) {

            ps.setInt(1, employeeId);

            ResultSet rs = ps.executeQuery();
            if (rs.next()) {
                int empId = rs.getInt("employee_id");
                String eName = rs.getString("employee_name");
                String email = rs.getString("email");
                Double salary = rs.getDouble("salary");
                BigDecimal bonus = rs.getBigDecimal("bonus");
                Date date = rs.getDate("date_of_joining");

                employee = new Employee();
                employee.setEmployeeName(eName);
                employee.setBonus(bonus);
                employee.setDoj(date);
                employee.setEmail(email);
                employee.setEmployee_id(empId);
                employee.setSalary(salary);
            }

        } catch (Exception e) {
            e.printStackTrace();
        }

        return employee;
    }

    @Override
    public void updateEmployeeEmailById(String newEmail, Integer employeeId) {

        String SQL = "UPDATE employee_table set email=? WHERE employee_id=?;";
        try(Connection connection = DBUtil.getConnection();PreparedStatement ps = connection.prepareStatement(SQL)) {

            ps.setString(1, newEmail);
            ps.setInt(2,employeeId);

            int executeUpdate = ps.executeUpdate();

            if(executeUpdate ==1){
                System.out.println("Employee email is updated..");
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    @Override
    public void deleteEmployeeById(Integer employeeId) {

        String SQL = "DELETE FROM employee_table WHERE employee_id=?;";
        try(Connection connection = DBUtil.getConnection();PreparedStatement ps = connection.prepareStatement(SQL)) {

            ps.setInt(1,employeeId);

            int executeUpdate = ps.executeUpdate();

            if(executeUpdate ==1){
                System.out.println("Employee is deleted with ID::"+employeeId);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }

    }

    @Override
    public List<Employee> getAllEmployeesInfo() {

        List<Employee> empList = new ArrayList<>();
        String SQL = "SELECT *FROM employee_table";
        try(Connection connection = DBUtil.getConnection();PreparedStatement ps = connection.prepareStatement(SQL)) {

            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                int empId = rs.getInt("employee_id");
                String eName = rs.getString("employee_name");
                String email = rs.getString("email");
                Double salary = rs.getDouble("salary");
                BigDecimal bonus = rs.getBigDecimal("bonus");
                Date date = rs.getDate("date_of_joining");

                Employee employee = new Employee();
                employee.setEmployeeName(eName);
                employee.setBonus(bonus);
                employee.setDoj(date);
                employee.setEmail(email);
                employee.setEmployee_id(empId);
                employee.setSalary(salary);

                empList.add(employee);
            }

        } catch (Exception e) {
            e.printStackTrace();
        }
        return empList;
    }
}

4.6创建JDBCTest测试上述代码

package com.demos;

import java.math.BigDecimal;
import java.util.Date;
import java.util.List;

import com.demos.dao.EmployeeDAO;
import com.demos.dao.impl.EmployeeDAOImpl;
import com.demos.model.Employee;

public class JDBCTest {

    public static void main(String[] args) {
        try {
            EmployeeDAO employeeDAO = new EmployeeDAOImpl();
            Employee employee = getEmployee();
            employeeDAO.createEmployee(employee);
            //getEmployeeById(employeeDAO);
            //employeeDAO.updateEmployeeEmailById("sam.cs2014@gmail.com", 4);
            //employeeDAO.deleteEmployeeById(6);
            //getAllEmployeesInfo(employeeDAO);
        }
        catch (Exception ex) {
            String msg=ex.toString();
            String error=ex.toString();
        }
    }

    private static void getAllEmployeesInfo(EmployeeDAO employeeDAO) {
        List<Employee> empList = employeeDAO.getAllEmployeesInfo();
        for (Employee employee : empList) {
            System.out.println(employee);
        }
    }

    private static void getEmployeeById(EmployeeDAO employeeDAO) {
        Employee employee2 = employeeDAO.getEmployeeById(4);
        if (employee2 != null) {
            System.out.println(employee2);
        } else {
            System.out.println("Employee does not exist..");
        }
    }

    private static Employee getEmployee() {
        Employee employee = new Employee();
        employee.setBonus(new BigDecimal(290));
        employee.setDoj(new Date());
        employee.setEmployeeName("John");
        employee.setEmail("jonh.cs2016@yahoo.com");
        employee.setSalary(90000.00);
        return employee;
    }
}

回到顶部

5测试程序

在JDBCTest代码块中右击，选择Debug 'JDBCTest.main()'，开始调试程序