Spring Data 01:JDBCアクセスMySQL

目次
Spring Data 01:JDBCアクセスMySQL
Spring Data 02:Spring JdbcTemplate MySQLアクセス
Spring Data 03:JPA入門
Spring Data 04:Spring Data JPA入門
Spring Data 05:Spring Data JPA+SpringBoot 2統合マルチデータソース
一、依存を追加する:jdbcのmysql接続駆動

    
      mysql
      mysql-connector-java
      8.0.15
    

    
      junit
      junit
      4.11
      test
    
  

二、JDBCUtilツールクラスの追加:Connectionを取得し、Connection、Statement、ResultSetを閉じる

package com.springboot.utils;
/**
 * author: x
 * Date:6/21/2019 11:03 AM
 */

import java.io.IOException;
import java.sql.*;


public class JDBCUtil {

    /**
     * @Description   Connection
     * @Return     JDBC Connection
     *           ，      ，      .      ，     
     * */
    public static Connection getConnection() throws ClassNotFoundException, SQLException, IOException {
        String url = "jdbc:mysql://localhost:3306/student";
        String user = "root";
        String password = "root";
        String driverClass = "com.mysql.jdbc.Driver";

        Class.forName(driverClass);
        Connection connection = DriverManager.getConnection(url,user,password);
        return connection;
    }

    /**
     *   DB     
      */
    public static void release(ResultSet resultSet, Statement statement, Connection connection){
        if (resultSet != null){
            try {
                resultSet.clearWarnings();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (statement != null){
            try {
                statement.clearWarnings();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (connection != null){
            try {
                connection.clearWarnings();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

三、studentデータベースとテーブルの確立

CREATE TABLE `student` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;

四、Entity:Studentの作成

package com.springboot.domain;


public class Student {
    private int id;
    private String name;
    private int age;

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public int getAge() {
        return age;
    }

    public void setAge(int age) {
        this.age = age;
    }
}

5 Studentのdaoインタフェースと実装インタフェースの作成:StudentDAO

package com.springboot.dao;

import com.springboot.domain.Student;
import java.util.List;

public interface StudentDAO {
    //      
    public List query();

    //    
    public void save(Student student);
}

実装:StudentDAOImpl

package com.springboot.dao;

import com.springboot.domain.Student;
import com.springboot.utils.JDBCUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

public class StudentDAOImpl implements StudentDAO {
    @Override
    public List query() {

        ArrayList students = new ArrayList<>();

        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        String sql = "select id, name, age from student";

        try {
                connection = JDBCUtil.getConnection();
                preparedStatement = connection.prepareStatement(sql);
                resultSet = preparedStatement.executeQuery();

                Student student = null;
                while (resultSet.next()){
                    int id = resultSet.getInt("id");
                    String name = resultSet.getString("name");
                    int age = resultSet.getInt("age");

                    student = new Student();
                    student.setId(id);
                    student.setName(name);
                    student.setAge(age);

                    students.add(student);
                }

        }catch (Exception e){
            e.printStackTrace();
        }finally {
            JDBCUtil.release(resultSet, preparedStatement, connection);
        }
        return students;
    }

    @Override
    public void save(Student student) {

        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        String sql = "insert into student(name ,age) values (?,?)";
        try {
            connection = JDBCUtil.getConnection();
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1,student.getName());
            preparedStatement.setInt(2,student.getAge());
            preparedStatement.executeUpdate();

        }catch (Exception e){
            e.printStackTrace();
        }finally {
            JDBCUtil.release(resultSet, preparedStatement, connection);
        }
    }
}

六、StudentDAOImplのユニットテストを作成する:StudentDAOImplTest

package com.springboot.utils.dao;

import com.springboot.dao.StudentDAOImpl;
import com.springboot.domain.Student;
import org.junit.Test;

import java.util.List;

public class StudentDAOImplTest {

    //       
    @Test
    public void testQuery(){
        StudentDAOImpl studentDAO = new StudentDAOImpl();
        List students = studentDAO.query();
        for (Student student:students){
            System.out.println("id"+student.getId()
                    +"name"+student.getName()
                    +"age"+student.getAge()
            );
        }

    }

    //       
    @Test
    public void testSave(){
        Student student = new Student();
        student.setName("  ");
        student.setAge(25);
        StudentDAOImpl studentDAO = new StudentDAOImpl();
        studentDAO.save(student);
    }
}

附:データベース接続情報をプロファイルに入れる書き方

は、以下のようにコードを修正する

.

        InputStream inputStream = JDBCUtil.class.getClassLoader().getResourceAsStream("db.properties");
        Properties properties = new Properties();
        properties.load(inputStream);

        String url = properties.getProperty("jdbc.url");
        String user = ("jdbc.user");
        String password = ("jdbc.password");
        String driverClass = ("jdbc.driverClass");

resourcesディレクトリにdb.propertiesデータベース接続プロファイル

を新規作成する.

jdbc.url = jdbc:mysql:///student;
jdbc.user = root;
jdbc.password = root;
jdbc.driverClass = com.mysql.jdbc.Driver;