Spring detailed jdbcTemplate
jdbcTemplate (concept and preparation)
1. What is jdbcTemplate
(1) The Spring framework encapsulates JDBC and uses jdbcTemplate to facilitate database operations
2. Preparation
(1) Introduce dependencies
(2) Configure the database connection pool in the spring configuration file
<!--Configure database connection pool--> <bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource" destroy-method="close"> <property name="url" value="jdbc:mysql:///db_stu"/> <property name="username" value="root"/> <property name="password" value="123456"/> <property name="driverClassName" value="com.mysql.jdbc.Driver"/> </bean>
(3) Configure the JdbcTemplate object and inject DataSource
<!--create JdbcTemplate object--> <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate"> <!--injection dataSource--> <property name="dataSource" ref="dataSource"></property> </bean>
(4) Create the service class dao class, inject the dao object into the service, inject the jdbcTemplate object into the dao
<!--Turn on component scanning--> <context:component-scan base-package="com.yxm.spring5"></context:component-scan>
@Service public class BookService { //inject dao @Autowired private BookDao bookDao; }
@Repository public class BookDaoImpl implements BookDao{ //Inject JdbcTemplate @Autowired private JdbcTemplate jdbcTemplate; }
3, JdbcTemplate operation database (add)
1. Create an entity class
public class User { private Integer userId; private String username; private String ustatus;
2. Write service and dao
(1) Perform database addition operation in dao
(2) Call the update method in the jdbcTemplate object to implement the add operation
* has two parameters
The first parameter: sql statement
The second parameter: variable parameter, set the SQL statement value
//added method @Override public void add(User user) { //Create SQL statements String sql = "insert into t_user values(null,?,?)"; //call method implementation Object[] args = {user.getUsername(), user.getUstatus()}; int update = jdbcTemplate.update(sql, args); System.out.println(update); }
3. Test
@Test public void testJdbcTemplate(){ ApplicationContext context = new ClassPathXmlApplicationContext("bean1.xml"); BookService bookService = context.getBean("bookService", BookService.class); User user = new User(); user.setUsername("yuan"); user.setUstatus("1"); bookService.addBook(user); }
4. JdbcTemplate operates the database (modify and delete)
//modified method @Override public void updateUser(User user) { //create sql statement String sql = "update t_user set username=?,ustatus=? where user_id=?"; int update = jdbcTemplate.update(sql, user.getUsername(), user.getUstatus(), user.getUserId()); System.out.println(update); } //delete method @Override public void deleteUser(int id) { //create sql statement String sql = "delete from t_user where user_id=?"; int update = jdbcTemplate.update(sql,id); System.out.println(update); }
//test class @Test public void testJdbcTemplate(){ ApplicationContext context = new ClassPathXmlApplicationContext("bean1.xml"); BookService bookService = context.getBean("bookService", BookService.class); //Add to User user = new User(); user.setUsername("yuan"); user.setUstatus("1"); bookService.addBook(user); //Revise User user1 = new User(); user1.setUsername("xin"); user1.setUstatus("0"); user1.setUserId(1); bookService.updateUser(user1); //delete bookService.deleteUser(2); }
5. JdbcTemplate operates the database (query)
1. The query returns a value
(1) How many records are in the query table and return a certain value
(2) Use JdbcTemplate to implement the query to return a certain value code
has two parameters
*The first parameter: sql statement
*The second parameter: return value type Class
//query table records @Override public int selectCount() { String sql = "select count(*) from t_user"; Integer count = jdbcTemplate.queryForObject(sql, Integer.class); return count; }
2. The query returns the object
(1) Scenario: query user details
(2) JdbcTemplate implements the query to return objects
three parameters
*The first parameter: SQL statement
*The second parameter: RowMapper is an interface that returns different types of data. Use the implementation class in this interface to complete the data encapsulation
* The third parameter: in the sql statement? the value of
//query returns object @Override public User findOne(int id) { String sql = "select * from t_user where user_id=?"; User user = jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<User>(User.class), id); return user; }
3. The query returns a collection
(1) Scenario: Query user list pagination
(2) Call the JdbcTemplate method to realize the query and return the collection
three parameters
*The first parameter: SQL statement
*The second parameter: RowMapper is an interface that returns different types of data. Use the implementation class in this interface to complete the data encapsulation
* The third parameter: in the sql statement? the value of
//The query returns a collection @Override public List<User> findAll() { String sql = "select * from t_user"; List<User> list = jdbcTemplate.query(sql,new BeanPropertyRowMapper<User>(User.class)); return list; }
6. JdbcTemplate operates the database (batch processing)
1. Batch processing: multiple pieces of data in the operation table
2. JdbcTemplate realizes batch adding operation
two parameters
*The first parameter: sql statement
*The second parameter: List collection, the data of multiple records added
//batch add @Override public void batchAdd(List<Object[]> list) { String sql = "update t_user set username=?,ustatus=? where user_id=?"; int[] ints = jdbcTemplate.batchUpdate(sql, list); System.out.println(Arrays.toString(ints)); }
//Batch add operation test List<Object[]> list1 = new ArrayList<>(); Object[] o1 = {"1","1"}; Object[] o2 = {"2","2"}; Object[] o3 = {"3","3"}; list1.add(o1); list1.add(o2); list1.add(o3); bookService.batchAdd(list1);
3. JdbcTemplate implements batch modification operations
//Batch Edit @Override public void batchUpdate(List<Object[]> list) { String sql = "update t_user set username=?,ustatus=? where user_id=?"; int[] ints = jdbcTemplate.batchUpdate(sql, list); System.out.println(Arrays.toString(ints)); }
//Batch modification operation test List<Object[]> list1 = new ArrayList<>(); Object[] o1 = {"2","10",5}; Object[] o2 = {"3","20",6}; Object[] o3 = {"4","30",7}; list1.add(o1); list1.add(o2); list1.add(o3); bookService.batchUpdate(list1);
3. JdbcTemplate realizes batch delete operation
//batch deletion @Override public void batchDelete(List<Object[]> list) { String sql = "delete from t_user where user_id=?"; int[] ints = jdbcTemplate.batchUpdate(sql, list); System.out.println(Arrays.toString(ints)); }
//Bulk delete operation test List<Object[]> list1 = new ArrayList<>(); Object[] o1 = {5}; Object[] o2 = {6}; Object[] o3 = {7}; list1.add(o1); list1.add(o2); list1.add(o3); bookService.batchDelete(list1);
=?";
int[] ints = jdbcTemplate.batchUpdate(sql, list);
System.out.println(Arrays.toString(ints));
}
```java //Bulk delete operation test List<Object[]> list1 = new ArrayList<>(); Object[] o1 = {5}; Object[] o2 = {6}; Object[] o3 = {7}; list1.add(o1); list1.add(o2); list1.add(o3); bookService.batchDelete(list1);