jdbc增删改查

DBUtil.java

package util;

import java.sql.*;

public class DBUtil {

    private static final String URL = "jdbc:mysql://localhost:3306/imooc";
    private static final String USER = "root";
    private static final String PASSWORD = "123";
    private static Connection connection = null;

    static{
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            connection = DriverManager.getConnection(URL, USER, PASSWORD);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

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

Goddess.java

package model;

import java.util.Date;

public class Goddess {
    private Integer id;
    private String user_name;
    private Integer sex;
    private Integer age;
    private Date birthday;
    private String email;
    private String mobile;
    private String create_user;
    private String update_user;
    private Date create_date;
    private Date update_date;
    private Integer isDel;

    //get、set方法
}

GoddessDao.java

package dao;

import model.Goddess;
import util.DBUtil;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class GoddessDao {

    public void addGoddess(Goddess goddess) throws SQLException {
        Connection connection = DBUtil.getConnection();
        PreparedStatement preparedStatement = connection.prepareStatement("insert into imooc_goddess(user_name, sex, age, birthday, email, mobile, create_user, create_date, update_user, update_date, isdel) values(?,?,?,?,?,?,?, current_date(), ?, current_date(), ?)");
        preparedStatement.setString(1, goddess.getUser_name());
        preparedStatement.setInt(2, goddess.getSex());
        preparedStatement.setInt(3, goddess.getAge());
        preparedStatement.setDate(4, new Date(goddess.getBirthday().getTime()));
        preparedStatement.setString(5, goddess.getEmail());
        preparedStatement.setString(6, goddess.getMobile());
        preparedStatement.setString(7, goddess.getCreate_user());
        preparedStatement.setString(8, goddess.getUpdate_user());
        preparedStatement.setInt(9, goddess.getIsDel());
        preparedStatement.execute();
    }

    public void updateGoddess(Goddess goddess) throws SQLException {
        Connection connection = DBUtil.getConnection();
        PreparedStatement preparedStatement = connection.prepareStatement("update imooc_goddess set user_name=?, sex=?, age=?, birthday=?, email=?, mobile=?, update_user=?, update_date=current_date(), isdel=? where id=?");
        preparedStatement.setString(1, goddess.getUser_name());
        preparedStatement.setInt(2, goddess.getSex());
        preparedStatement.setInt(3, goddess.getAge());
        preparedStatement.setDate(4, new Date(goddess.getBirthday().getTime()));
        preparedStatement.setString(5, goddess.getEmail());
        preparedStatement.setString(6, goddess.getMobile());
        preparedStatement.setString(7, goddess.getUpdate_user());
        preparedStatement.setInt(8, goddess.getIsDel());
        preparedStatement.setInt(9, goddess.getId());
        preparedStatement.execute();
    }

    public void delGoddess(int id) throws SQLException {
        Connection connection = DBUtil.getConnection();
        PreparedStatement preparedStatement = connection.prepareStatement("delete from imooc_goddess where id=?");
        preparedStatement.setInt(1, id);
        preparedStatement.execute();
    }

    public List<Goddess> queryAll() throws SQLException {
        Connection connection = DBUtil.getConnection();
        Statement statement = connection.createStatement();
        ResultSet resultSet = statement.executeQuery("select user_name, age from imooc_goddess");

        List<Goddess> goddessList = new ArrayList<>();
        while(resultSet.next()){
            Goddess goddess = new Goddess();
            goddess.setUser_name(resultSet.getString("user_name"));
            goddess.setAge(resultSet.getInt("age"));

            goddessList.add(goddess);
        }
        return goddessList;
    }

    public Goddess getById(int id) throws SQLException {
        Connection connection = DBUtil.getConnection();
        PreparedStatement preparedStatement = connection.prepareStatement("select user_name, age from imooc_goddess where id=?");
        preparedStatement.setInt(1, id);
        ResultSet resultSet = preparedStatement.executeQuery();

        Goddess goddess = null;
        while (resultSet.next()){
            goddess = new Goddess();
            goddess.setId(resultSet.getInt("id"));
            goddess.setUser_name(resultSet.getString("user_name"));
            goddess.setSex(resultSet.getInt("sex"));
            goddess.setAge(resultSet.getInt("age"));
            goddess.setBirthday(resultSet.getDate("birthday"));
            goddess.setEmail(resultSet.getString("email"));
            goddess.setMobile(resultSet.getString("mobile"));
            goddess.setCreate_user(resultSet.getString("create_user"));
            goddess.setUpdate_user(resultSet.getString("update_user"));
            goddess.setCreate_date(resultSet.getDate("create_date"));
            goddess.setUpdate_date(resultSet.getDate("update_date"));
            goddess.setIsDel(resultSet.getInt("isdel"));
        }
        return goddess;
    }
}

GoddessDaoTest.java

package dao;

import model.Goddess;
import org.junit.Test;

import java.sql.SQLException;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;

public class GoddessDaoTest {

    GoddessDao goddessDao = new GoddessDao();

    @Test
    public void addGoddessTest() throws ParseException, SQLException {
        SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-mm-dd");
        Date birthday = simpleDateFormat.parse("2019-09-02");

        Goddess goddess = new Goddess();
        goddess.setUser_name("y1");
        goddess.setSex(1);
        goddess.setAge(11);
        goddess.setBirthday(birthday);
        goddess.setEmail("2809890600@qq.com");
        goddess.setMobile("15612341234");
        goddess.setCreate_user("y0");
        goddess.setUpdate_user("y0");
        goddess.setIsDel(0);

        goddessDao.addGoddess(goddess);
    }

    @Test
    public void queryAllTest() throws SQLException {
        List<Goddess> goddessList = goddessDao.queryAll();
        for (int i=0; i<goddessList.size(); i++){
            Goddess goddess = goddessList.get(i);
            System.out.println(goddess.getUser_name() + ":" + goddess.getAge());
        }
    }
}

 pom.xml

<?xml version="1.0" encoding="UTF-8"?>

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
  <modelVersion>4.0.0</modelVersion>

  <groupId>com.yanguobin</groupId>
  <artifactId>jdbcDemo</artifactId>
  <version>1.0-SNAPSHOT</version>
  <packaging>war</packaging>

  <name>jdbcDemo Maven Webapp</name>
  <!-- FIXME change it to the project's website -->
  <url>http://www.example.com</url>

  <properties>
    <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
    <maven.compiler.source>1.7</maven.compiler.source>
    <maven.compiler.target>1.7</maven.compiler.target>
  </properties>

  <dependencies>
    <dependency>
      <groupId>junit</groupId>
      <artifactId>junit</artifactId>
      <version>4.11</version>
      <scope>test</scope>
    </dependency>

    <dependency>
      <groupId>mysql</groupId>
      <artifactId>mysql-connector-java</artifactId>
      <version>8.0.17</version>
    </dependency>
  </dependencies>

  <build>
    <finalName>jdbcDemo</finalName>
    <pluginManagement><!-- lock down plugins versions to avoid using Maven defaults (may be moved to parent pom) -->
      <plugins>
        <plugin>
          <artifactId>maven-clean-plugin</artifactId>
          <version>3.1.0</version>
        </plugin>
        <!-- see http://maven.apache.org/ref/current/maven-core/default-bindings.html#Plugin_bindings_for_war_packaging -->
        <plugin>
          <artifactId>maven-resources-plugin</artifactId>
          <version>3.0.2</version>
        </plugin>
        <plugin>
          <artifactId>maven-compiler-plugin</artifactId>
          <version>3.8.0</version>
        </plugin>
        <plugin>
          <artifactId>maven-surefire-plugin</artifactId>
          <version>2.22.1</version>
        </plugin>
        <plugin>
          <artifactId>maven-war-plugin</artifactId>
          <version>3.2.2</version>
        </plugin>
        <plugin>
          <artifactId>maven-install-plugin</artifactId>
          <version>2.5.2</version>
        </plugin>
        <plugin>
          <artifactId>maven-deploy-plugin</artifactId>
          <version>2.8.2</version>
        </plugin>
      </plugins>
    </pluginManagement>
  </build>
</project>