View Javadoc
1   /*
2    *    Copyright 2009-2023 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    *       https://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", "******",
64            "someone@apache.org", Null.LONGVARCHAR);
65        Map<String, Object> row = exec.selectOne("SELECT * FROM author WHERE username = ?", "someone");
66        connection.rollback();
67        assertTrue(SqlRunner.NO_GENERATED_KEY != id);
68        assertEquals("someone", row.get("USERNAME"));
69      }
70    }
71  
72    @Test
73    void shouldUpdateCategory() throws Exception {
74      DataSource ds = createUnpooledDataSource(JPETSTORE_PROPERTIES);
75      runScript(ds, JPETSTORE_DDL);
76      runScript(ds, JPETSTORE_DATA);
77      try (Connection connection = ds.getConnection()) {
78        SqlRunner exec = new SqlRunner(connection);
79        int count = exec.update("update product set category = ? where productid = ?", "DOGS", "FI-SW-01");
80        Map<String, Object> row = exec.selectOne("SELECT * FROM PRODUCT WHERE PRODUCTID = ?", "FI-SW-01");
81        assertEquals("DOGS", row.get("CATEGORY"));
82        assertEquals(1, count);
83      }
84    }
85  
86    @Test
87    void shouldDeleteOne() throws Exception {
88      DataSource ds = createUnpooledDataSource(JPETSTORE_PROPERTIES);
89      runScript(ds, JPETSTORE_DDL);
90      runScript(ds, JPETSTORE_DATA);
91      try (Connection connection = ds.getConnection()) {
92        SqlRunner exec = new SqlRunner(connection);
93        int count = exec.delete("delete from item");
94        List<Map<String, Object>> rows = exec.selectAll("SELECT * FROM ITEM");
95        assertEquals(28, count);
96        assertEquals(0, rows.size());
97      }
98    }
99  
100   @Test
101   void shouldDemonstrateDDLThroughRunMethod() throws Exception {
102     DataSource ds = createUnpooledDataSource(JPETSTORE_PROPERTIES);
103     try (Connection connection = ds.getConnection()) {
104       SqlRunner exec = new SqlRunner(connection);
105       exec.run("CREATE TABLE BLAH(ID INTEGER)");
106       exec.run("insert into BLAH values (1)");
107       List<Map<String, Object>> rows = exec.selectAll("SELECT * FROM BLAH");
108       exec.run("DROP TABLE BLAH");
109       assertEquals(1, rows.size());
110     }
111   }
112 }