【使用MyBatis连接ORACL数据库】
前期准备,参考Spring Boot实践应用开发(3),建立名为sample的项目
1,在src/main下创建resources文件夹,在buildpath里设置为source,输出文件夹默认bin。
2,在resources文件夹下创建文件application.yml,输入数据连接信息。(数据库连接信息及用户名密码按各自实际环境设置)
# DB Connection Setting
spring.datasource.url: jdbc:oracle:thin:@localhost:1521:orcl
spring.datasource.username: sample
spring.datasource.password: sample
spring.datasource.driver-class-name: oracle.jdbc.OracleDriver
3,在resources目录下创建文件夹lib,把oracle连接驱动ojdbc6.jar拷贝到lib文件夹下。
4,修改build.gradle文件,追加mybatis库,数据连接相关库,以及日志输出相关库。
buildscript {
repositories {
jcenter()
mavenLocal()
mavenCentral()
maven { url"http://repo.spring.io/snapshot" }
maven { url"http://repo.spring.io/milestone" }
maven { url"https://repo.spring.io/libs-release" }
maven { url"https://repo.spring.io/plugins-snapshot" }
}
dependencies {
classpath("org.springframework.boot:spring-boot-gradle-plugin:1.2.3.RELEASE")
}
}
apply plugin:'java'
apply plugin:'eclipse'
apply plugin:'spring-boot'
jar {
baseName= 'sample'
version= '0.1.0'
}
repositories {
jcenter()
mavenLocal()
mavenCentral()
maven { url"http://repo.spring.io/snapshot" }
maven { url"http://repo.spring.io/milestone" }
maven { url"https://repo.spring.io/libs-release" }
maven { url"https://repo.spring.io/plugins-snapshot" }
}
sourceCompatibility = 1.8
targetCompatibility = 1.8
dependencies {
compile("org.springframework.boot:spring-boot-starter-web")
compile("org.springframework.boot:spring-boot-starter-data-jpa")// added for DB connection on 20150509
compile("org.springframework.boot:spring-boot-starter-jdbc") // added for DB connection on20150509
compile(files("./src/main/resources/lib/ojdbc6.jar")) // added for DBconnection on 20150509
compile("com.h2database:h2") // added for DB connection on 20150509
compile("org.mybatis:mybatis:3.2.8") // added for mybatis on 20150509
compile("org.mybatis:mybatis-spring:1.2.2") // added for mybatis on 20150509
compile("org.lazyluke:log4jdbc-remix:0.2.7")// added for slf4j on 20150509
compile("org.projectlombok:lombok:1.16.2") // added for lombok on 20150509
testCompile("org.springframework.boot:spring-boot-starter-test")
}
task wrapper(type: Wrapper) {
gradleVersion = '2.3'
}
6, 在resources文件夹下创建文件logback.xml,设定日志输出信息。
<?xml version="1.0"encoding="UTF-8"?>
<configuration scan="true"scanPeriod="30 seconds">
<include resource="org/springframework/boot/logging/logback/base.xml"/>
<logger name="jdbc"level="off" />
<logger name="jdbc.sqltiming"level="info" />
</configuration>
7,在数据库端执行初始化SQL语句。
CREATE TABLE users (username VARCHAR(100) NOT NULL PRIMARY KEY, password VARCHAR(100), role VARCHAR(100));
INSERT INTO users (username,password, role) VALUES ('User', 'demo', 'User');
INSERT INTO users (username,password, role) VALUES ('Admin', 'demo', 'Admin');
commit;
8,在resources文件夹下创建文件夹org\com\sample\mapper,然后新建UserMapper.xml,记入数据操作SQL语句。
<?xmlversion="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC"-//mybatis.org//DTD Mapper 3.0//EN""http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mappernamespace="org.com.sample.mapper.UserMapper">
<select id="selectAllUser"resultType="org.com.sample.dataset.User">
SELECT username, password, role FROM
users
</select>
<select id="selectUserByUsername"useCache="false"
parameterType="String"resultType="org.com.sample.dataset.User">
SELECT username, password, role FROM
users
WHERE username=#{username}
</select>
<insert id="insertUser"parameterType="org.com.sample.dataset.User">
INSERT INTO users
(
username,
password,
role
)
VALUES
(
#{username},
#{password},
#{role}
<!-- if we want to handle nulldata, we can set "jdbcType", below is sample.
#{username ,jdbcType=VARCHAR},
#{password ,jdbcType=VARCHAR},
#{role ,jdbcType=VARCHAR} -->
)
</insert>
<update id="updateUserByUsername"parameterType="org.com.sample.dataset.User">
UPDATE users
<set>
<iftest="password != null">password=#{password}</if>
<iftest="role != null">role=#{role}</if>
</set>
WHERE username=#{username}
</update>
<delete id="deleteUserByUsername"parameterType="String">
DELETE FROM
users
WHERE username=#{username}
</delete>
</mapper>
9,按下面层次新建包及JAVA类。
Application.java
package org.com.sample;
importorg.springframework.boot.*;
importorg.springframework.boot.autoconfigure.*;
@SpringBootApplication
public class Application {
public static void main(String[] args) throws Exception {
SpringApplicationapp = new SpringApplication(Application.class);
app.setShowBanner(false); // turn off thebanner
app.run(args);
}
}
ApplicationConfig.java
package org.com.sample;
import java.io.File;
importjavax.servlet.Filter;
importjavax.sql.DataSource;
importnet.sf.log4jdbc.Log4jdbcProxyDataSource;
importorg.apache.ibatis.session.SqlSessionFactory;
importorg.com.sample.mapper.UserMapper;
importorg.mybatis.spring.SqlSessionFactoryBean;
importorg.mybatis.spring.SqlSessionTemplate;
importorg.springframework.beans.factory.annotation.Autowired;
importorg.springframework.boot.autoconfigure.jdbc.DataSourceBuilder;
importorg.springframework.boot.autoconfigure.jdbc.DataSourceProperties;
importorg.springframework.context.annotation.Bean;
importorg.springframework.context.annotation.Configuration;
importorg.springframework.core.Ordered;
importorg.springframework.core.annotation.Order;
import org.springframework.core.io.ClassPathResource;
importorg.springframework.core.io.Resource;
importorg.springframework.web.filter.CharacterEncodingFilter;
@Configuration
public class ApplicationConfig {
@Autowired
DataSourcePropertiesdataSourceProperties;
DataSourcedataSource;
@Bean
DataSourcerealDataSource() {
DataSourceBuilderfactory = DataSourceBuilder
.create(this.dataSourceProperties.getClassLoader())
.url(this.dataSourceProperties.getUrl())
.username(this.dataSourceProperties.getUsername())
.password(this.dataSourceProperties.getPassword());
this.dataSource = factory.build();
return newLog4jdbcProxyDataSource(this.dataSource);
}
@Bean
public SqlSessionFactorygetSqlSessionFactory() {
Resource[]resources = new Resource[] { getMapperXMLPathResource(UserMapper.class)
};
SqlSessionFactoryBeanfactoryBean = newSqlSessionFactoryBean();
factoryBean.setDataSource(realDataSource());
factoryBean.setMapperLocations(resources);
SqlSessionFactorysqlSessionFactory = null;
try {
sqlSessionFactory = factoryBean.getObject();
}catch (Exception e) {
e.printStackTrace();
System.exit(0);
}
org.apache.ibatis.session.Configurationconfiguration = sqlSessionFactory
.getConfiguration();
configuration.setMapUnderscoreToCamelCase(true);
return sqlSessionFactory;
}
public static ResourcegetMapperXMLPathResource(Class<?> clazz) {
return new ClassPathResource(clazz.getName()
.replace(".", File.separator).concat(".xml"));
}
@Bean
public UserMappergetUserMapper() {
SqlSessionTemplatesessionTemplate = new SqlSessionTemplate(
getSqlSessionFactory());
return sessionTemplate.getMapper(UserMapper.class);
}
@Order(Ordered.HIGHEST_PRECEDENCE)
@Bean
FiltercharacterEncodingFilter() {
CharacterEncodingFilterfilter = newCharacterEncodingFilter();
filter.setEncoding("UTF-8");
filter.setForceEncoding(true);
return filter;
}
}
UserController.java
packageorg.com.sample.controller;
import java.net.URI;
import java.util.List;
importorg.com.sample.dataset.User;
importorg.com.sample.facade.UserFacade;
import org.slf4j.Logger;
importorg.slf4j.LoggerFactory;
importorg.springframework.beans.factory.annotation.Autowired;
importorg.springframework.http.HttpHeaders;
importorg.springframework.http.HttpStatus;
importorg.springframework.http.ResponseEntity;
importorg.springframework.web.bind.annotation.PathVariable;
importorg.springframework.web.bind.annotation.RequestBody;
importorg.springframework.web.bind.annotation.RequestMapping;
importorg.springframework.web.bind.annotation.RequestMethod;
importorg.springframework.web.bind.annotation.RestController;
importorg.springframework.web.util.UriComponentsBuilder;
@RestController
@RequestMapping("user")
public class UserController {
private static final Logger logger = LoggerFactory
.getLogger(UserController.class);
@Autowired
UserFacadeuserFacade;
@RequestMapping(method =RequestMethod.GET)
List<User>selectAllUser() {
List<User>userList = userFacade.selectAllUser();
return userList;
}
@RequestMapping(value = "{username}", method =RequestMethod.GET)
UserselectUser(@PathVariable String username) {
Useruser = userFacade.selectUserByUsername(username);
logger.info("###incontroller user=[" + user.toString() + "]###");
return user;
}
@RequestMapping(method =RequestMethod.POST)
ResponseEntity<User>insertUser(@RequestBody User user,
UriComponentsBuilderuriBuilder) {
userFacade.insertUser(user);
UserinsertedUser = userFacade.selectUserByUsername(user.getUsername());
URIlocation = uriBuilder.path("user/{username}")
.buildAndExpand(insertedUser.getUsername()).toUri();
HttpHeadersheaders = new HttpHeaders();
headers.setLocation(location);
return newResponseEntity<>(insertedUser, headers, HttpStatus.CREATED);
}
@RequestMapping(value = "{username}", method =RequestMethod.PUT)
UserupdateUser(@PathVariable String username, @RequestBody User user) {
user.setUsername(username);
userFacade.updateUserByUsername(user);
return userFacade.selectUserByUsername(username);
}
@RequestMapping(value = "{username}", method =RequestMethod.DELETE)
void updateUser(@PathVariable String username) {
userFacade.deleteUserByUsername(username);
}
}
User .java
packageorg.com.sample.dataset;
importjavax.persistence.Entity;
importjavax.persistence.Id;
importjavax.persistence.Table;
importlombok.AllArgsConstructor;
import lombok.Data;
importlombok.NoArgsConstructor;
import com.fasterxml.jackson.annotation.JsonIgnore;
@Data
@NoArgsConstructor
@AllArgsConstructor
@Entity
@Table(name = "users")
public class User {
@Id
private String username;
// Comment:if set@JsonIgnore, We can't use curl to send and get data
// @JsonIgnore
private String password;
// @JsonIgnore
private String role;
}
UserFacade.java
packageorg.com.sample.facade;
import java.util.List;
importorg.com.sample.dataset.User;
public interface UserFacade {
public List<User> selectAllUser();
public User selectUserByUsername(String username);
public int insertUser(Useruser);
public int updateUserByUsername(User user);
public int deleteUserByUsername(String username);
}
UserFacadeImpl.java
packageorg.com.sample.facade;
import java.util.List;
importorg.com.sample.dataset.User;
importorg.com.sample.service.UserService;
importorg.springframework.beans.factory.annotation.Autowired;
importorg.springframework.stereotype.Service;
importorg.springframework.transaction.annotation.Transactional;
@Service
@Transactional
public class UserFacadeImpl implements UserFacade {
@Autowired
UserServiceuserService;
@Override
public List<User>selectAllUser() {
return userService.selectAllUser();
}
@Override
public UserselectUserByUsername(String username) {
return userService.selectUserByUsername(username);
}
@Override
public int insertUser(User user) {
return userService.insertUser(user);
}
@Override
public intupdateUserByUsername(User user) {
return userService.updateUserByUsername(user);
}
@Override
public intdeleteUserByUsername(String username) {
return userService.deleteUserByUsername(username);
}
}
UserMapper.java
packageorg.com.sample.mapper;
import java.util.List;
importorg.com.sample.dataset.User;
public interface UserMapper {
public List<User> selectAllUser();
public User selectUserByUsername(String username);
public int insertUser(Useruser);
public int updateUserByUsername(User user);
public int deleteUserByUsername(Stringusername);
}
UserService.java
packageorg.com.sample.service;
import java.util.List;
importorg.com.sample.dataset.User;
importorg.com.sample.mapper.UserMapper;
import org.slf4j.Logger;
importorg.slf4j.LoggerFactory;
importorg.springframework.beans.factory.annotation.Autowired;
importorg.springframework.stereotype.Service;
@Service
public class UserService {
private static final Logger logger = LoggerFactory
.getLogger(UserService.class);
@Autowired
UserMapperuserMapper;
public List<User>selectAllUser() {
List<User>userList = userMapper.selectAllUser();
return userList;
}
public UserselectUserByUsername(String username) {
Useruser = userMapper.selectUserByUsername(username);
logger.info("###in serviceuser=[" + user.toString() + "]###");
return user;
}
public int insertUser(User user) {
return userMapper.insertUser(user);
}
public intupdateUserByUsername(User user) {
return userMapper.updateUserByUsername(user);
}
public intdeleteUserByUsername(String username) {
return userMapper.deleteUserByUsername(username);
}
}
10,执行 gradlew eclipse 命令
11,执行 gradlew build 命令
12,执行 gradlew bootRun 命令
13,用curl进行检证
取全件,curl http://localhost:8080/user -v -X GET
取username是Admin的1件,curlhttp://localhost:8080/user/Admin -v -X GET
插入1件username是User1的数据,
curl http://localhost:8080/user -v -X POST -H"Content-Type: application/json"-d "{\"username\":\"User1\",\"password\":\"demo\" ,\"role\":\"User\"}"
把username是User1的role更新为Admin,
curl http://localhost:8080/user/User1 -v -X PUT -H"Content-Type: application/json"-d "{\"role\" : \"Admin\"}"
删除username是User1的数据,
curl http://localhost:8080/user/User1 -v -X DELETE
源代码已上传到百度云盘,http://pan.baidu.com/s/1c2GaAFA,second.zip