중고헌터 - STS, DB(MySQL ) 연결

DBConnectionTest.java

 

실행하면 아래에 오류가 발생한다.

package com.jcy.usedhunter;

import java.sql.*;

public class DBConnectionTest {
    public static void main(String[] args) throws Exception {
        // 스키마의 이름(springbasic)이 다른 경우 알맞게 변경해야 함
        String DB_URL = "jdbc:mysql://localhost:3301/springbasic?useUnicode=true&characterEncoding=utf8";

        // DB의 userid와 pwd를 알맞게 변경해야 함
        String DB_USER = "jcy";
        String DB_PASSWORD = "darknight24!";

        Connection conn = DriverManager.getConnection(DB_URL, DB_USER, DB_PASSWORD); // 데이터베이스의 연결을 얻는다.
        Statement stmt  = conn.createStatement(); // Statement를 생성한다.

        String query = "SELECT now()"; // 시스템의 현재 날짜시간을 출력하는 쿼리(query)
        ResultSet rs = stmt.executeQuery(query); // query를 실행한 결과를 rs에 담는다.

        // 실행결과가 담긴 rs에서 한 줄씩 읽어서 출력
        while (rs.next()) {
            String curDate = rs.getString(1);  // 읽어온 행의 첫번째 컬럼의 값을 String으로 읽어서 curDate에 저장
            System.out.println(curDate);       // 2022-01-11 13:53:00.0
        }
    }
}

Exception in thread "main" java.sql.SQLException: No suitable driver found for jdbc:mysql://localhost:3301/springbasic?useUnicode=true&characterEncoding=utf8
	at java.sql/java.sql.DriverManager.getConnection(DriverManager.java:702)
	at java.sql/java.sql.DriverManager.getConnection(DriverManager.java:228)
	at com.jcy.usedhunter.DBConnectionTest.main(DBConnectionTest.java:14)

 

MVNRepository  에서 코드 복사

 

<!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>8.0.30</version>
</dependency>

 

 

 

Spring JDBC 로 해보자

 

 

 

<!-- https://mvnrepository.com/artifact/org.springframework/spring-jdbc -->
<dependency>
    <groupId>org.springframework</groupId>
    <artifactId>spring-jdbc</artifactId>
    <version>5.3.23</version>
</dependency>

	<!-- https://mvnrepository.com/artifact/org.springframework/spring-jdbc -->
		<dependency>
			<groupId>org.springframework</groupId>
			<artifactId>spring-jdbc</artifactId>
			<version>${org.springframework-version}</version>
		</dependency>

 

 

DBConnectionTest2.java

 

package com.jcy.usedhunter;


import java.sql.Connection;

import javax.sql.DataSource;

import org.springframework.context.ApplicationContext;
import org.springframework.context.support.GenericXmlApplicationContext;
import org.springframework.jdbc.datasource.DriverManagerDataSource;


public class DBConnectionTest2 {
	public static void main(String[] args) throws Exception{
      // 스키마의 이름(usedhunter)이 다른 경우 알맞게 변경
      String DB_URL = "jdbc:mysql://localhost:3301/usedhunter?useUnicode=true&characterEncoding=utf8";

      // DB의 userid와 pwd를 알맞게 변경
      String DB_USER = "jcy";
      String DB_PASSWORD = "zz!";
      String DB_DRIVER = "com.mysql.jdbc.Driver";

      DriverManagerDataSource ds = new DriverManagerDataSource();
      ds.setDriverClassName(DB_DRIVER);
      ds.setUrl(DB_URL);
      ds.setUsername(DB_USER);
      ds.setPassword(DB_PASSWORD);
	    
//      ApplicationContext ac = new GenericXmlApplicationContext("file:src/main/webapp/WEB-INF/spring/**/root-context.xml");
//      DataSource ds = ac.getBean(DataSource.class);

      Connection conn = ds.getConnection(); // 데이터베이스의 연결을 얻는다.

      System.out.println("conn = " + conn);
//      assertTrue(conn!=null);
	}

}

Loading class `com.mysql.jdbc.Driver'. This is deprecated. The new driver class is `com.mysql.cj.jdbc.Driver'. The driver is automatically registered via the SPI and manual loading of the driver class is generally unnecessary.
conn = com.mysql.cj.jdbc.ConnectionImpl@2d9caaeb

 

 

DriverManagerDataSource 를 빈으로 등록하자

 

root.context.xml

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns:mvc="http://www.springframework.org/schema/mvc"
	   xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	   xmlns="http://www.springframework.org/schema/beans"
	   xmlns:context="http://www.springframework.org/schema/context"
	   xmlns:tx="http://www.springframework.org/schema/tx"
	   xsi:schemaLocation="http://www.springframework.org/schema/mvc https://www.springframework.org/schema/mvc/spring-mvc.xsd
		http://www.springframework.org/schema/beans https://www.springframework.org/schema/beans/spring-beans.xsd
		http://www.springframework.org/schema/context https://www.springframework.org/schema/context/spring-context.xsd http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx.xsd">

	<bean id="dataSource"
		class="org.springframework.jdbc.datasource.DriverManagerDataSource">
		<property name="driverClassName"
			value="com.mysql.jdbc.Driver"></property>
		<property name="url"
			value="jdbc:mysql://localhost:3301/usedhunter?useUnicode=true&amp;characterEncoding=utf8"></property>
		<property name="username" value="jcy"></property>
		<property name="password" value="zz!"></property>
	</bean>
	</beans>

 

 

다시 실행

 

conn = com.mysql.cj.jdbc.ConnectionImpl@675d8c96

 

 

 

TDD(Test Driven Development) 로 테스트 해보자

 

Junit4 lib 추가

 

 

DBConnectionTest2Test

package com.jcy.usedhunter;

import static org.junit.Assert.assertTrue;

import java.sql.Connection;

import javax.sql.DataSource;

import org.junit.Test;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.GenericXmlApplicationContext;

public class DBConnectionTest2Test{
	
	@Test
	public void main() throws Exception{
		ApplicationContext ac = new GenericXmlApplicationContext("file:src/main/webapp/WEB-INF/spring/**/root-context.xml");
	      DataSource ds = ac.getBean(DataSource.class);

	      Connection conn = ds.getConnection(); // 데이터베이스의 연결을 얻는다.

	      System.out.println("conn = " + conn);
	      assertTrue(conn!=null); // 괄호 안의 조건식이 true면 테스트 성공, 아니면 실패
	}
}

 

 

 

 

 

		<!-- Test -->
	<!-- https://mvnrepository.com/artifact/org.springframework/spring-test -->
	<dependency>
		<groupId>org.springframework</groupId>
		<artifactId>spring-test</artifactId>
		<version>${org.springframework-version}</version>
		<scope>test</scope>
	</dependency>

package com.jcy.usedhunter;

import static org.junit.Assert.assertTrue;

import java.sql.Connection;

import javax.sql.DataSource;

import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;

@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration(locations = {"file:src/main/webapp/WEB-INF/spring/**/root-context.xml"})
public class DBConnectionTest2Test{
	
	@Autowired
	DataSource ds;
	
	@Test
	public void main() throws Exception{
//		ApplicationContext ac = new GenericXmlApplicationContext("file:src/main/webapp/WEB-INF/spring/**/root-context.xml");
//	      DataSource ds = ac.getBean(DataSource.class);

	      Connection conn = ds.getConnection(); // 데이터베이스의 연결을 얻는다.

	      System.out.println("conn = " + conn);
	      assertTrue(conn!=null); // 괄호 안의 조건식이 true면 테스트 성공, 아니면 실패
	}
}

 

 

 

MySQL 테이블 생성 테스트