Skip to content

Commit e37e5b4

Browse files
dunwudunwu
committed
sqlite java api demo
1 parent d479218 commit e37e5b4

File tree

5 files changed

+241
-0
lines changed

5 files changed

+241
-0
lines changed

‎codes/javadb/javadb-sqlite/pom.xml

Lines changed: 51 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,51 @@
1+
<?xml version="1.0" encoding="UTF-8"?>
2+
<project xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://maven.apache.org/POM/4.0.0"
3+
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
4+
<modelVersion>4.0.0</modelVersion>
5+
6+
<parent>
7+
<groupId>org.springframework.boot</groupId>
8+
<artifactId>spring-boot-starter-parent</artifactId>
9+
<version>2.1.1.RELEASE</version>
10+
</parent>
11+
12+
<groupId>io.github.dunwu.db</groupId>
13+
<artifactId>sqlite-demo</artifactId>
14+
<name>SQLite Demo</name>
15+
<version>1.0.0</version>
16+
<packaging>jar</packaging>
17+
18+
<properties>
19+
<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
20+
<project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
21+
<java.version>1.8</java.version>
22+
</properties>
23+
24+
<dependencies>
25+
<dependency>
26+
<groupId>org.springframework.boot</groupId>
27+
<artifactId>spring-boot-starter</artifactId>
28+
</dependency>
29+
<dependency>
30+
<groupId>org.springframework.boot</groupId>
31+
<artifactId>spring-boot-starter-test</artifactId>
32+
<scope>test</scope>
33+
</dependency>
34+
35+
<dependency>
36+
<groupId>org.xerial</groupId>
37+
<artifactId>sqlite-jdbc</artifactId>
38+
<version>3.25.2</version>
39+
</dependency>
40+
</dependencies>
41+
42+
<build>
43+
<finalName>${project.artifactId}</finalName>
44+
<plugins>
45+
<plugin>
46+
<groupId>org.springframework.boot</groupId>
47+
<artifactId>spring-boot-maven-plugin</artifactId>
48+
</plugin>
49+
</plugins>
50+
</build>
51+
</project>

‎codes/javadb/javadb-sqlite/src/main/java/io/github/dunwu/db/SqliteApplication.java

Lines changed: 19 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,19 @@
1+
package io.github.dunwu.db;
2+
3+
import org.springframework.boot.CommandLineRunner;
4+
import org.springframework.boot.builder.SpringApplicationBuilder;
5+
6+
/**
7+
* @author Zhang Peng
8+
* @date 2019-03-05
9+
*/
10+
public class SqliteApplication implements CommandLineRunner {
11+
public static void main(String[] args) {
12+
new SpringApplicationBuilder(SqliteApplication.class).run(args);
13+
}
14+
15+
@Override
16+
public void run(String... args) {
17+
SqliteDemo.main(null);
18+
}
19+
}

‎codes/javadb/javadb-sqlite/src/main/java/io/github/dunwu/db/SqliteDemo.java

Lines changed: 166 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,166 @@
1+
package io.github.dunwu.db;
2+
3+
import java.sql.Connection;
4+
import java.sql.DriverManager;
5+
import java.sql.ResultSet;
6+
import java.sql.Statement;
7+
8+
/**
9+
* @author Zhang Peng
10+
* @date 2019-03-05
11+
*/
12+
public class SqliteDemo {
13+
14+
public static void createTable() {
15+
try {
16+
Class.forName("org.sqlite.JDBC");
17+
Connection connection = DriverManager.getConnection("jdbc:sqlite:test.db");
18+
19+
Statement statement = connection.createStatement();
20+
String sql = new StringBuilder().append("CREATE TABLE COMPANY ").append("(ID INT PRIMARY KEY NOT NULL,")
21+
.append(" NAME TEXT NOT NULL, ")
22+
.append(" AGE INT NOT NULL, ")
23+
.append(" ADDRESS CHAR(50), ").append(" SALARY REAL)")
24+
.toString();
25+
statement.executeUpdate(sql);
26+
statement.close();
27+
connection.close();
28+
} catch (Exception e) {
29+
System.err.println(e.getClass().getName() + ": " + e.getMessage());
30+
System.exit(0);
31+
}
32+
System.out.println("Create table successfully.");
33+
}
34+
35+
public static void dropTable() {
36+
try {
37+
Class.forName("org.sqlite.JDBC");
38+
Connection connection = DriverManager.getConnection("jdbc:sqlite:test.db");
39+
40+
Statement statement = connection.createStatement();
41+
String sql = new StringBuilder().append("DROP TABLE IF EXISTS COMPANY;").toString();
42+
statement.executeUpdate(sql);
43+
statement.close();
44+
connection.close();
45+
} catch (Exception e) {
46+
System.err.println(e.getClass().getName() + ": " + e.getMessage());
47+
System.exit(0);
48+
}
49+
System.out.println("Drop table successfully.");
50+
}
51+
52+
public static void insert() {
53+
try {
54+
Class.forName("org.sqlite.JDBC");
55+
Connection connection = DriverManager.getConnection("jdbc:sqlite:test.db");
56+
connection.setAutoCommit(false);
57+
58+
Statement statement = connection.createStatement();
59+
String sql = "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) "
60+
+ "VALUES (1, 'Paul', 32, 'California', 20000.00 );";
61+
statement.executeUpdate(sql);
62+
63+
sql = "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) " + "VALUES (2, 'Allen', 25, 'Texas', 15000.00 );";
64+
statement.executeUpdate(sql);
65+
66+
sql = "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) " + "VALUES (3, 'Teddy', 23, 'Norway', 20000.00 );";
67+
statement.executeUpdate(sql);
68+
69+
sql = "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) "
70+
+ "VALUES (4, 'Mark', 25, 'Rich-Mond ', 65000.00 );";
71+
statement.executeUpdate(sql);
72+
73+
statement.close();
74+
connection.commit();
75+
connection.close();
76+
} catch (Exception e) {
77+
System.err.println(e.getClass().getName() + ": " + e.getMessage());
78+
System.exit(0);
79+
}
80+
System.out.println("Insert table successfully.");
81+
}
82+
83+
public static void delete() {
84+
try {
85+
Class.forName("org.sqlite.JDBC");
86+
Connection connection = DriverManager.getConnection("jdbc:sqlite:test.db");
87+
connection.setAutoCommit(false);
88+
89+
Statement statement = connection.createStatement();
90+
String sql = "DELETE from COMPANY where ID=2;";
91+
statement.executeUpdate(sql);
92+
93+
String sql2 = "DELETE from COMPANY where ID=3;";
94+
statement.executeUpdate(sql2);
95+
96+
String sql3 = "DELETE from COMPANY where ID=4;";
97+
statement.executeUpdate(sql3);
98+
connection.commit();
99+
100+
statement.close();
101+
connection.close();
102+
} catch (Exception e) {
103+
System.err.println(e.getClass().getName() + ": " + e.getMessage());
104+
System.exit(0);
105+
}
106+
System.out.println("Delete table successfully.");
107+
}
108+
109+
public static void update() {
110+
try {
111+
Class.forName("org.sqlite.JDBC");
112+
Connection connection = DriverManager.getConnection("jdbc:sqlite:test.db");
113+
connection.setAutoCommit(false);
114+
115+
Statement statement = connection.createStatement();
116+
String sql = "UPDATE COMPANY set SALARY = 25000.00 where ID=1;";
117+
statement.executeUpdate(sql);
118+
connection.commit();
119+
120+
statement.close();
121+
connection.close();
122+
} catch (Exception e) {
123+
System.err.println(e.getClass().getName() + ": " + e.getMessage());
124+
System.exit(0);
125+
}
126+
System.out.println("Update table successfully.");
127+
}
128+
129+
public static void select() {
130+
try {
131+
Class.forName("org.sqlite.JDBC");
132+
Connection connection = DriverManager.getConnection("jdbc:sqlite:test.db");
133+
connection.setAutoCommit(false);
134+
135+
Statement statement = connection.createStatement();
136+
ResultSet resultSet = statement.executeQuery("SELECT * FROM COMPANY;");
137+
while (resultSet.next()) {
138+
int id = resultSet.getInt("id");
139+
String name = resultSet.getString("name");
140+
int age = resultSet.getInt("age");
141+
String address = resultSet.getString("address");
142+
float salary = resultSet.getFloat("salary");
143+
String format = String
144+
.format("ID = %s, NAME = %s, AGE = %d, ADDRESS = %s, SALARY = %f", id, name, age, address, salary);
145+
System.out.println(format);
146+
}
147+
resultSet.close();
148+
statement.close();
149+
connection.close();
150+
} catch (Exception e) {
151+
System.err.println(e.getClass().getName() + ": " + e.getMessage());
152+
System.exit(0);
153+
}
154+
}
155+
156+
public static void main(String[] args) {
157+
SqliteDemo.dropTable();
158+
SqliteDemo.createTable();
159+
SqliteDemo.insert();
160+
SqliteDemo.select();
161+
SqliteDemo.delete();
162+
SqliteDemo.select();
163+
SqliteDemo.update();
164+
SqliteDemo.select();
165+
}
166+
}

‎codes/javadb/javadb-sqlite/src/main/resources/application.properties

Whitespace-only changes.

‎codes/javadb/javadb-sqlite/src/main/resources/logback.xml

Lines changed: 5 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,5 @@
1+
<?xml version="1.0" encoding="UTF-8"?>
2+
<configuration>
3+
<include resource="org/springframework/boot/logging/logback/base.xml"/>
4+
<logger name="io.github.dunwu" level="DEBUG"/>
5+
</configuration>

0 commit comments

Comments
 (0)