JAVA entry [19]-JdbcTemplate simple instance, java-jdbctemplate

JAVA entry [19]-JdbcTemplate simple instance, java-jdbctemplate
1. About JdbcTemplate

JdbcTemplate is the most basic Spring JDBC template, which supports Simple JDBC database access and query based on index parameters.

Spring data access template: a large part of repetitive work in database operations, such as transaction control, resource management, and Exception Handling. Spring's template class processes these fixed parts. At the same time, application-related data access is processed in callback implementation, including statements, binding parameters, and sorting results. In this way, we only need to care about our own data access logic.

Ii. Spring JdbcTemplate instance

Our learning goal is to write a demo to implement the CRUD operation on Category.

1. Create a table

Create a database store in mysql and run the following SQL statement:

Create table Category (Id int not null, Name varchar (80) null, constraint pk_category primary key (Id); insert into category (id, Name) VALUES (1, 'Female '); insert into category (id, Name) VALUES (2, 'makeup'); insert into category (id, Name) VALUES (3, 'book ');Db_store. SQL

2. The ide I use is IdeaIU. I use maven to build a project and configure spring through xml. The code structure after completion is:

Public class Category {private int cateId; private String cateName; public int getCateId () {return cateId;} public void setCateId (int cateId) {this. cateId = cateId;} public String getCateName () {return cateName;} public void setCateName (String cateName) {this. cateName = cateName ;}@ Override public String toString () {return "id =" + cateId + "name =" + cateName ;}}

　　

4. Modify pom. xml to introduce related dependencies.

<Dependencies> <dependency> <groupId> junit </groupId> <artifactId> junit </artifactId> <version> 4.12 </version> </dependency> <! -- Mysql database link jar package --> <dependency> <groupId> mysql </groupId> <artifactId> mysql-connector-java </artifactId> <version> 5.1.21 </version> <scope> runtime </scope> </dependency> </dependencies>

　　

5. Configure applicationContext. xml

You need to configure dataSource as the data source of jdbcTemplate. Then configure CategoryDao bean to construct and inject the jdbcTemplate object. The complete applicationContext. xml is as follows:

<?xml version="1.0" encoding="UTF-8"?><beans xmlns="http://www.springframework.org/schema/beans"       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"       xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.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:3306/store"></property>        <property name="username" value="root"></property>        <property name="password" value="root"></property>    </bean>    <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">        <property name="dataSource" ref="dataSource"></property>    </bean>        <bean id="categoryDao" class="CategoryDao">        <constructor-arg ref="jdbcTemplate"></constructor-arg>    </bean></beans>

　　

6. Data Access Implementation class CategoryDao

The CategoryDao constructor contains the jdbcTemplate parameter and implements Common Data Access Operations. As you can see, we only need to pay attention to specific SQL statements. In addition, the lambda syntax is used in the getById () and getAll () methods.

Import org. springframework. jdbc. core. jdbcTemplate; import org. springframework. jdbc. core. rowMapper; import java. SQL. resultSet; import java. SQL. SQLException; import java. util. list; import java. util. map;/*** Created by Chen Jing on. */public class CategoryDao {private JdbcTemplate jdbcTemplate; public CategoryDao (JdbcTemplate jdbcTemplate) {this. jdbcTemplate = jdbcTemplate;} public int add (Category c Ategory) {String SQL = "INSERT INTO category (id, name) VALUES (?,?) "; Return jdbcTemplate. update (SQL, category. getCateId (), category. getCateName ();} public int update (Category category) {String SQL = "UPDATE Category SET Name =? WHERE Id =? "; Return jdbcTemplate. update (SQL, category. getCateName (), category. getCateId ();} public int delete (int id) {String SQL = "DELETE FROM Category WHERE Id =? "; Return jdbcTemplate. update (SQL, id);} public int count () {String SQL = "SELECT COUNT (0) FROM Category"; return jdbcTemplate. queryForObject (SQL, Integer. class);} public Category getById (int id) {String SQL = "SELECT Id, Name FROM Category WHERE Id =? "; Return jdbcTemplate. queryForObject (SQL, (ResultSet rs, int rowNumber)-> {Category category = new Category (); category. setCateId (rs. getInt ("Id"); category. setCateName (rs. getString ("Name"); return category ;}, id) ;}public List <Category> getAll () {String SQL = "SELECT Id, Name FROM Category "; list <Category> result = jdbcTemplate. query (SQL, (resultSet, I)-> {Category category = new Category (); category. setCateId (resultSet. getInt ("Id"); category. setCateName (resultSet. getString ("Name"); return category;}); return result ;}}

　　

7. Test

@ ContextConfiguration (locations = "classpath: applicationContext. xml ") @ RunWith (SpringJUnit4ClassRunner. class) public class testCategoryDao {@ Autowired private CategoryDao categoryDao; @ Test public void testAdd () {Category category = new Category (); category. setCateId (4); category. setCateName ("Mother and Baby"); int result = categoryDao. add (category); System. out. println (result) ;}@ Test public void testUpdate () {Category category = new Category (); category. setCateId (4); category. setCateName ("men's wear"); int result = categoryDao. update (category); System. out. println (result) ;}@ Test public void testGetById () {int id = 4; Category category = categoryDao. getById (id); System. out. println (category. toString () ;}@ Test public void testGetAll () {List <Category> categories = categoryDao. getAll (); for (Category item: categories) {System. out. println (item) ;}@ Test public void testCount () {int count = categoryDao. count (); System. out. println (count) ;}@ Test public void testDelete () {int id = 4; int result = categoryDao. delete (id); System. out. println (result );}}

　　

Full: https://github.com/cathychen00/learnjava/tree/master/DemoJdbcTemplate