View Javadoc
1   /*
2    *    Copyright 2009-2021 the original author or authors.
3    *
4    *    Licensed under the Apache License, Version 2.0 (the "License");
5    *    you may not use this file except in compliance with the License.
6    *    You may obtain a copy of the License at
7    *
8    *       http://www.apache.org/licenses/LICENSE-2.0
9    *
10   *    Unless required by applicable law or agreed to in writing, software
11   *    distributed under the License is distributed on an "AS IS" BASIS,
12   *    WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
13   *    See the License for the specific language governing permissions and
14   *    limitations under the License.
15   */
16  package org.apache.ibatis.jdbc;
17  
18  import static org.junit.jupiter.api.Assertions.assertEquals;
19  import static org.junit.jupiter.api.Assertions.assertTrue;
20  
21  import java.sql.Connection;
22  import java.util.List;
23  import java.util.Map;
24  
25  import javax.sql.DataSource;
26  
27  import org.apache.ibatis.BaseDataTest;
28  import org.junit.jupiter.api.Test;
29  
30  class SqlRunnerTest extends BaseDataTest {
31  
32    @Test
33    void shouldSelectOne() throws Exception {
34      DataSource ds = createUnpooledDataSource(JPETSTORE_PROPERTIES);
35      runScript(ds, JPETSTORE_DDL);
36      runScript(ds, JPETSTORE_DATA);
37      try (Connection connection = ds.getConnection()) {
38        SqlRunner exec = new SqlRunner(connection);
39        Map<String, Object> row = exec.selectOne("SELECT * FROM PRODUCT WHERE PRODUCTID = ?", "FI-SW-01");
40        assertEquals("FI-SW-01", row.get("PRODUCTID"));
41      }
42    }
43  
44    @Test
45    void shouldSelectList() throws Exception {
46      DataSource ds = createUnpooledDataSource(JPETSTORE_PROPERTIES);
47      runScript(ds, JPETSTORE_DDL);
48      runScript(ds, JPETSTORE_DATA);
49      try (Connection connection = ds.getConnection()) {
50        SqlRunner exec = new SqlRunner(connection);
51        List<Map<String, Object>> rows = exec.selectAll("SELECT * FROM PRODUCT");
52        assertEquals(16, rows.size());
53      }
54    }
55  
56    @Test
57    void shouldInsert() throws Exception {
58      DataSource ds = createUnpooledDataSource(BLOG_PROPERTIES);
59      runScript(ds, BLOG_DDL);
60      try (Connection connection = ds.getConnection()) {
61        SqlRunner exec = new SqlRunner(connection);
62        exec.setUseGeneratedKeySupport(true);
63        int id = exec.insert("INSERT INTO author (username, password, email, bio) VALUES (?,?,?,?)", "someone", "******", "someone@apache.org", Null.LONGVARCHAR);
64        Map<String, Object> row = exec.selectOne("SELECT * FROM author WHERE username = ?", "someone");
65        connection.rollback();
66        assertTrue(SqlRunner.NO_GENERATED_KEY != id);
67        assertEquals("someone", row.get("USERNAME"));
68      }
69    }
70  
71    @Test
72    void shouldUpdateCategory() throws Exception {
73      DataSource ds = createUnpooledDataSource(JPETSTORE_PROPERTIES);
74      runScript(ds, JPETSTORE_DDL);
75      runScript(ds, JPETSTORE_DATA);
76      try (Connection connection = ds.getConnection()) {
77        SqlRunner exec = new SqlRunner(connection);
78        int count = exec.update("update product set category = ? where productid = ?", "DOGS", "FI-SW-01");
79        Map<String, Object> row = exec.selectOne("SELECT * FROM PRODUCT WHERE PRODUCTID = ?", "FI-SW-01");
80        assertEquals("DOGS", row.get("CATEGORY"));
81        assertEquals(1, count);
82      }
83    }
84  
85    @Test
86    void shouldDeleteOne() throws Exception {
87      DataSource ds = createUnpooledDataSource(JPETSTORE_PROPERTIES);
88      runScript(ds, JPETSTORE_DDL);
89      runScript(ds, JPETSTORE_DATA);
90      try (Connection connection = ds.getConnection()) {
91        SqlRunner exec = new SqlRunner(connection);
92        int count = exec.delete("delete from item");
93        List<Map<String, Object>> rows = exec.selectAll("SELECT * FROM ITEM");
94        assertEquals(28, count);
95        assertEquals(0, rows.size());
96      }
97    }
98  
99    @Test
100   void shouldDemonstrateDDLThroughRunMethod() throws Exception {
101     DataSource ds = createUnpooledDataSource(JPETSTORE_PROPERTIES);
102     try (Connection connection = ds.getConnection()) {
103       SqlRunner exec = new SqlRunner(connection);
104       exec.run("CREATE TABLE BLAH(ID INTEGER)");
105       exec.run("insert into BLAH values (1)");
106       List<Map<String, Object>> rows = exec.selectAll("SELECT * FROM BLAH");
107       exec.run("DROP TABLE BLAH");
108       assertEquals(1, rows.size());
109     }
110   }
111 }