Skip to content

Commit 0bcd924

Browse files
mercyblitzmercyblitz
committed
Add lesson 6
1 parent ddba6fb commit 0bcd924

File tree

11 files changed

+401
-0
lines changed

11 files changed

+401
-0
lines changed

‎spring-boot/lesson-6/Spring Boot 数据库 JDBC.jpg

107 KB
Loading

‎spring-boot/lesson-6/Spring Boot 系列之数据库 JDBC.pptx

138 KB
Binary file not shown.

‎spring-boot/lesson-6/spring-boot-lesson-6/.gitignore

Lines changed: 24 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,24 @@
1+
target/
2+
!.mvn/wrapper/maven-wrapper.jar
3+
4+
### STS ###
5+
.apt_generated
6+
.classpath
7+
.factorypath
8+
.project
9+
.settings
10+
.springBeans
11+
12+
### IntelliJ IDEA ###
13+
.idea
14+
*.iws
15+
*.iml
16+
*.ipr
17+
18+
### NetBeans ###
19+
nbproject/private/
20+
build/
21+
nbbuild/
22+
dist/
23+
nbdist/
24+
.nb-gradle/

‎spring-boot/lesson-6/spring-boot-lesson-6/pom.xml

Lines changed: 71 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,71 @@
1+
<?xml version="1.0" encoding="UTF-8"?>
2+
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
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+
<groupId>com.segmentfault</groupId>
7+
<artifactId>spring-boot-lesson-6</artifactId>
8+
<version>0.0.1-SNAPSHOT</version>
9+
<packaging>jar</packaging>
10+
11+
<name>spring-boot-lesson-6</name>
12+
<description>Demo project for Spring Boot</description>
13+
14+
<parent>
15+
<groupId>org.springframework.boot</groupId>
16+
<artifactId>spring-boot-starter-parent</artifactId>
17+
<version>1.5.4.RELEASE</version>
18+
<relativePath/> <!-- lookup parent from repository -->
19+
</parent>
20+
21+
<properties>
22+
<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
23+
<project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
24+
<java.version>1.8</java.version>
25+
</properties>
26+
27+
<dependencies>
28+
29+
<dependency>
30+
<groupId>org.springframework.boot</groupId>
31+
<artifactId>spring-boot-starter-jdbc</artifactId>
32+
</dependency>
33+
34+
<dependency>
35+
<groupId>org.springframework</groupId>
36+
<artifactId>spring-tx</artifactId>
37+
</dependency>
38+
39+
<dependency>
40+
<groupId>commons-dbcp</groupId>
41+
<artifactId>commons-dbcp</artifactId>
42+
</dependency>
43+
44+
<dependency>
45+
<groupId>org.springframework.boot</groupId>
46+
<artifactId>spring-boot-starter-web</artifactId>
47+
</dependency>
48+
49+
<dependency>
50+
<groupId>mysql</groupId>
51+
<artifactId>mysql-connector-java</artifactId>
52+
<scope>runtime</scope>
53+
</dependency>
54+
<dependency>
55+
<groupId>org.springframework.boot</groupId>
56+
<artifactId>spring-boot-starter-test</artifactId>
57+
<scope>test</scope>
58+
</dependency>
59+
</dependencies>
60+
61+
<build>
62+
<plugins>
63+
<plugin>
64+
<groupId>org.springframework.boot</groupId>
65+
<artifactId>spring-boot-maven-plugin</artifactId>
66+
</plugin>
67+
</plugins>
68+
</build>
69+
70+
71+
</project>

‎spring-boot/lesson-6/spring-boot-lesson-6/src/main/java/com/segmentfault/springbootlesson6/JdbcController.java

Lines changed: 151 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,151 @@
1+
package com.segmentfault.springbootlesson6;
2+
3+
import com.segmentfault.springbootlesson6.domain.User;
4+
import com.segmentfault.springbootlesson6.domain.UserServiceImpl;
5+
import com.segmentfault.springbootlesson6.service.UserService;
6+
import org.springframework.beans.factory.annotation.Autowired;
7+
import org.springframework.dao.DataAccessException;
8+
import org.springframework.jdbc.core.JdbcTemplate;
9+
import org.springframework.jdbc.core.StatementCallback;
10+
import org.springframework.web.bind.annotation.*;
11+
12+
import javax.sql.DataSource;
13+
import java.sql.*;
14+
import java.util.*;
15+
16+
@RestController
17+
public class JdbcController {
18+
19+
@Autowired
20+
private DataSource dataSource;
21+
22+
@Autowired
23+
private UserService userService;
24+
25+
@Autowired
26+
private JdbcTemplate jdbcTemplate;
27+
28+
@RequestMapping("/jdbc/meta/transaction/supported")
29+
public boolean supportedTransaction() {
30+
31+
boolean supported = false;
32+
33+
Connection connection = null;
34+
35+
try {
36+
connection = dataSource.getConnection();
37+
38+
DatabaseMetaData databaseMetaData = connection.getMetaData();
39+
40+
supported = databaseMetaData.supportsTransactions();
41+
42+
} catch (SQLException e) {
43+
throw new RuntimeException(e);
44+
}
45+
46+
return supported;
47+
48+
}
49+
50+
@RequestMapping("/users")
51+
public List<Map<String, Object>> getUsers() {
52+
53+
return jdbcTemplate.execute(new StatementCallback<List<Map<String, Object>>>() {
54+
@Override
55+
public List<Map<String, Object>> doInStatement(Statement stmt) throws SQLException, DataAccessException {
56+
57+
ResultSet resultSet = stmt.executeQuery("SELECT * FROM user");
58+
59+
ResultSetMetaData resultSetMetaData = resultSet.getMetaData();
60+
61+
int columnCount = resultSetMetaData.getColumnCount();
62+
63+
List<String> columnNames = new ArrayList<>(columnCount);
64+
65+
for (int i = 1; i <= columnCount; i++) {
66+
String columnName = resultSetMetaData.getColumnName(i);
67+
columnNames.add(columnName);
68+
}
69+
70+
List<Map<String, Object>> data = new LinkedList<>();
71+
72+
while (resultSet.next()) {
73+
74+
Map<String, Object> columnData = new LinkedHashMap<>();
75+
76+
for (String columnName : columnNames) {
77+
78+
Object columnValue = resultSet.getObject(columnName);
79+
80+
columnData.put(columnName, columnValue);
81+
82+
}
83+
84+
data.add(columnData);
85+
86+
87+
}
88+
return data;
89+
}
90+
});
91+
}
92+
93+
94+
@RequestMapping("/user/get")
95+
public Map<String, Object> getUser(@RequestParam(value = "id", defaultValue = "1") int id) {
96+
97+
Map<String, Object> data = new HashMap<String, Object>();
98+
Connection connection = null;
99+
Savepoint savepoint = null;
100+
try {
101+
connection = dataSource.getConnection();
102+
connection.setAutoCommit(false);
103+
// savepoint = connection.setSavepoint();
104+
PreparedStatement statement = connection.prepareStatement("SELECT id,name,age FROM user WHERE id= ?");
105+
statement.setInt(1, id);
106+
ResultSet resultSet = statement.executeQuery();
107+
108+
while (resultSet.next()) {
109+
110+
int id_ = resultSet.getInt("id");
111+
String name = resultSet.getString("name");
112+
int age = resultSet.getInt("age");
113+
114+
data.put("id", id);
115+
data.put("name", name);
116+
data.put("age", age);
117+
118+
}
119+
120+
connection.commit();
121+
122+
} catch (SQLException e) {
123+
// if (connection != null) {
124+
// connection.rollback(savepoint);
125+
// }
126+
e.printStackTrace();
127+
} finally {
128+
try {
129+
connection.setAutoCommit(true);
130+
connection.close();
131+
} catch (SQLException e) {
132+
e.printStackTrace();
133+
}
134+
}
135+
136+
137+
return data;
138+
139+
}
140+
141+
@RequestMapping(value = "/user/add", method = RequestMethod.POST)
142+
@ResponseBody
143+
public Map<String, Object> addUser(@RequestBody User user) {
144+
Map<String, Object> data = new HashMap<String, Object>();
145+
146+
data.put("success", userService.save(user));
147+
data.put("success", userService.save2(user));
148+
149+
return data;
150+
}
151+
}

‎spring-boot/lesson-6/spring-boot-lesson-6/src/main/java/com/segmentfault/springbootlesson6/SpringBootLesson6Application.java

Lines changed: 12 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,12 @@
1+
package com.segmentfault.springbootlesson6;
2+
3+
import org.springframework.boot.SpringApplication;
4+
import org.springframework.boot.autoconfigure.SpringBootApplication;
5+
6+
@SpringBootApplication
7+
public class SpringBootLesson6Application {
8+
9+
public static void main(String[] args) {
10+
SpringApplication.run(SpringBootLesson6Application.class, args);
11+
}
12+
}

‎spring-boot/lesson-6/spring-boot-lesson-6/src/main/java/com/segmentfault/springbootlesson6/domain/User.java

Lines changed: 35 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,35 @@
1+
package com.segmentfault.springbootlesson6.domain;
2+
3+
4+
public class User {
5+
6+
private int id;
7+
8+
private String name;
9+
10+
private int age;
11+
12+
public int getId() {
13+
return id;
14+
}
15+
16+
public void setId(int id) {
17+
this.id = id;
18+
}
19+
20+
public String getName() {
21+
return name;
22+
}
23+
24+
public void setName(String name) {
25+
this.name = name;
26+
}
27+
28+
public int getAge() {
29+
return age;
30+
}
31+
32+
public void setAge(int age) {
33+
this.age = age;
34+
}
35+
}

‎spring-boot/lesson-6/spring-boot-lesson-6/src/main/java/com/segmentfault/springbootlesson6/domain/UserServiceImpl.java

Lines changed: 65 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,65 @@
1+
package com.segmentfault.springbootlesson6.domain;
2+
3+
4+
import com.segmentfault.springbootlesson6.service.UserService;
5+
import org.springframework.beans.factory.annotation.Autowired;
6+
import org.springframework.dao.DataAccessException;
7+
import org.springframework.jdbc.core.JdbcTemplate;
8+
import org.springframework.jdbc.core.PreparedStatementCallback;
9+
import org.springframework.stereotype.Service;
10+
import org.springframework.transaction.PlatformTransactionManager;
11+
import org.springframework.transaction.TransactionStatus;
12+
import org.springframework.transaction.annotation.EnableTransactionManagement;
13+
import org.springframework.transaction.annotation.Transactional;
14+
import org.springframework.transaction.support.DefaultTransactionDefinition;
15+
16+
import java.sql.PreparedStatement;
17+
import java.sql.SQLException;
18+
19+
20+
@Service
21+
@EnableTransactionManagement
22+
public class UserServiceImpl implements UserService {
23+
24+
@Autowired
25+
private JdbcTemplate jdbcTemplate;
26+
27+
@Autowired
28+
private PlatformTransactionManager platformTransactionManager;
29+
30+
@Override
31+
@Transactional
32+
public boolean save(User user) {
33+
34+
35+
Boolean result = jdbcTemplate.execute("INSERT INTO user(name,age) VALUES(?,?);", new PreparedStatementCallback<Boolean>() {
36+
37+
@Override
38+
public Boolean doInPreparedStatement(PreparedStatement ps) throws SQLException, DataAccessException {
39+
ps.setString(1, user.getName());
40+
ps.setInt(2, user.getAge());
41+
return ps.executeUpdate() > 0;
42+
}
43+
44+
});
45+
46+
return result;
47+
}
48+
49+
@Transactional
50+
public boolean save2(User user) {
51+
Boolean result = false;
52+
DefaultTransactionDefinition transactionDefinition = new DefaultTransactionDefinition();
53+
TransactionStatus transactionStatus = platformTransactionManager.getTransaction(transactionDefinition);
54+
try {
55+
result = save(user);
56+
platformTransactionManager.commit(transactionStatus);
57+
} catch (Exception e) {
58+
platformTransactionManager.rollback(transactionStatus);
59+
}
60+
61+
return result;
62+
}
63+
64+
65+
}

‎spring-boot/lesson-6/spring-boot-lesson-6/src/main/java/com/segmentfault/springbootlesson6/service/UserService.java

Lines changed: 13 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,13 @@
1+
package com.segmentfault.springbootlesson6.service;
2+
3+
4+
import com.segmentfault.springbootlesson6.domain.User;
5+
6+
public interface UserService {
7+
8+
9+
boolean save(User user);
10+
11+
boolean save2(User user);
12+
13+
}

‎spring-boot/lesson-6/spring-boot-lesson-6/src/main/resources/application.properties

Lines changed: 14 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,14 @@
1+
2+
spring.datasource.url = jdbc:mysql://localhost:3306/test
3+
spring.datasource.username = root
4+
spring.datasource.password = 123456
5+
spring.datasource.driverClassName = com.mysql.jdbc.Driver
6+
7+
spring.datasource.type = org.apache.commons.dbcp.BasicDataSource
8+
spring.datasource.dbcp.url = jdbc:mysql://localhost:3306/test
9+
spring.datasource.dbcp.driverClassName = com.mysql.jdbc.Driver
10+
spring.datasource.dbcp.username = root
11+
spring.datasource.dbcp.password = 123456
12+
spring.datasource.dbcp.maxActive = 20
13+
spring.datasource.dbcp.maxIdle = 5
14+
spring.datasource.dbcp.validationQuery = SELECT * FROM user;

0 commit comments

Comments
 (0)