mysql jdbc example_Spring + JDBC example

In this tutorial, we will extend last Maven + Spring hello world example by adding JDBC support, to use Spring + JDBC to insert a record into a customer table.

1. Customer table

In this example, we are using MySQL database.

CREATE TABLE`customer` (

`CUST_ID`int(10) unsigned NOT NULLAUTO_INCREMENT,

`NAME`varchar(100) NOT NULL,

`AGE`int(10) unsigned NOT NULL,PRIMARY KEY(`CUST_ID`)

) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

2. Project Dependency

Add Spring and MySQL dependencies in Maven pom.xml file.

File : pom.xml

http://maven.apache.org/maven-v4_0_0.xsd">

4.0.0

com.mkyong.common

SpringExample

jar

1.0-SNAPSHOT

SpringExample

http://maven.apache.org

org.springframework

spring

2.5.6

mysql

mysql-connector-java

5.1.9

3. Customer model

Add a customer model to store customer’s data.

packagecom.mkyong.customer.model;importjava.sql.Timestamp;public classCustomer

{intcustId;

String name;intage;//getter and setter methods

}

4. Data Access Object (DAO) pattern

Customer Dao interface.

packagecom.mkyong.customer.dao;importcom.mkyong.customer.model.Customer;public interfaceCustomerDAO

{public voidinsert(Customer customer);public Customer findByCustomerId(intcustId);

}

Customer Dao implementation, use JDBC to issue a simple insert and select statement.

packagecom.mkyong.customer.dao.impl;importjava.sql.Connection;importjava.sql.PreparedStatement;importjava.sql.ResultSet;importjava.sql.SQLException;importjavax.sql.DataSource;importcom.mkyong.customer.dao.CustomerDAO;importcom.mkyong.customer.model.Customer;public class JdbcCustomerDAO implementsCustomerDAO

{privateDataSource dataSource;public voidsetDataSource(DataSource dataSource) {this.dataSource =dataSource;

}public voidinsert(Customer customer){

String sql= "INSERT INTO CUSTOMER " +

"(CUST_ID, NAME, AGE) VALUES (?, ?, ?)";

Connection conn= null;try{

conn=dataSource.getConnection();

PreparedStatement ps=conn.prepareStatement(sql);

ps.setInt(1, customer.getCustId());

ps.setString(2, customer.getName());

ps.setInt(3, customer.getAge());

ps.executeUpdate();

ps.close();

}catch(SQLException e) {throw newRuntimeException(e);

}finally{if (conn != null) {try{

conn.close();

}catch(SQLException e) {}

}

}

}public Customer findByCustomerId(intcustId){

String sql= "SELECT * FROM CUSTOMER WHERE CUST_ID = ?";

Connection conn= null;try{

conn=dataSource.getConnection();

PreparedStatement ps=conn.prepareStatement(sql);

ps.setInt(1, custId);

Customer customer= null;

ResultSet rs=ps.executeQuery();if(rs.next()) {

customer= newCustomer(

rs.getInt("CUST_ID"),

rs.getString("NAME"),

rs.getInt("Age")

);

}

rs.close();

ps.close();returncustomer;

}catch(SQLException e) {throw newRuntimeException(e);

}finally{if (conn != null) {try{

conn.close();

}catch(SQLException e) {}

}

}

}

}

5. Spring bean configuration

Create the Spring bean configuration file for customerDAO and datasource.

File : Spring-Customer.xml

http://www.springframework.org/schema/beans/spring-beans-2.5.xsd">

File : Spring-Datasource.xml

http://www.springframework.org/schema/beans/spring-beans-2.5.xsd">

File : Spring-Module.xml

http://www.springframework.org/schema/beans/spring-beans-2.5.xsd">

6. Review project structure

Full directory structure of this example.

7. Run it

package com.mkyong.common;

import org.springframework.context.ApplicationContext;

import org.springframework.context.support.ClassPathXmlApplicationContext;

import com.mkyong.customer.dao.CustomerDAO;

import com.mkyong.customer.model.Customer;

public class App

{

public static void main( String[] args )

{

ApplicationContext context =

new ClassPathXmlApplicationContext("Spring-Module.xml");

CustomerDAO customerDAO = (CustomerDAO) context.getBean("customerDAO");

Customer customer = new Customer(1, "mkyong",28);

customerDAO.insert(customer);

Customer customer1 = customerDAO.findByCustomerId(1);

System.out.println(customer1);

}

}

output

Customer [age=28, custId=1, name=mkyong]

转 ： https://mkyong.com/spring/maven-spring-jdbc-example/