通过springboot操作读写数据库，首先在pom.xml引入对应的依赖jar包
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.1.10</version>
</dependency>
<!--通用mapper-->
<dependency>
<groupId>tk.mybatis</groupId>
<artifactId>mapper-spring-boot-starter</artifactId>
<version>2.1.5</version>
</dependency>
<!-- 数据库驱动 -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.19</version>
</dependency>
<!-- oracle ojdbc -->
<dependency>
<groupId>oracle</groupId>
<artifactId>ojdbc6</artifactId>
<version>11.2.0.3</version>
</dependency>
然后进入application.properties，写入链接数据库的地址
spring.datasource.type=com.alibaba.druid.pool.DruidDataSource
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource.url=jdbc:mysql://IP:3306/canace?useUnicode=true&characterEncoding=UTF-8&useSSL=false&allowMultiQueries=true
spring.datasource.username=root
spring.datasource.password=(密码)
创建数据库连接池
private static DruidDataSource dataSource;
static {
Properties properties = ProPertiesUtil.getProperties("application.properties");
// 使用德鲁伊数据库连接池工厂类
try {
dataSource = (DruidDataSource) DruidDataSourceFactory.createDataSource(properties);
} catch (Exception e) {
e.printStackTrace();
}
}
获取链接对象
/**
* 获取连接对象（从数据库连接池中获取）
* @return 连接对象
*/
public static Connection getConnection() {
Connection connection = null;
try {
connection = dataSource.getConnection();
} catch (Exception e) {
return null;
}
return connection;
}
通过sql语句写入mysql数据库（QueryRunner模式）
public void addUser(userAllModel use) {
//创建QueryRunner 对象
QueryRunner queryRunner = new QueryRunner();
//获取Connection连接
Connection connection = JDBCUtil.getConnection();
//定义sql语句
String sql = "insert into user(username,description,content) values(?,?,?)";
try {
int update = queryRunner.update(connection, sql, use.getUsername(), use.getDescription(), use.getContent());
} catch (SQLException e) {
e.printStackTrace();
} finally {
JDBCUtil.close(connection);
}
}
查询语句，结果返回格式是Map集合
public String selectLast() {
//创建QueryRunner 对象
QueryRunner queryRunner = new QueryRunner();
//获取Connection连接
Connection connection = JDBCUtil.getConnection();
//定义sql语句
String sql = "select * from user order by id desc limit 1";
try {
//这条记录返回Map集合
Map<String, Object> objectMap = queryRunner.query(connection, sql, new MapHandler());
return JSON.toJSONString(objectMap);
} catch (SQLException e) {
e.printStackTrace();
}
return null;
}
第二、下面是DriverManager的链接方式：
static String driverClass = "oracle.jdbc.driver.OracleDriver"; //oracle的驱动
static String url = "jdbc:oracle:thin:@IP:1521:ORCL"; //连接oracle
static String user = "System"; //user是数据库的用户名
static String password = "123456"; //用户登录密码
public static Connection getconn() { //为了方便下面的讲解，这里专门建立了一个用于数据库连接的一个方法
Connection conn = null;
try {
Class.forName(driverClass);//首先建立驱动
conn = DriverManager.getConnection(url, user, password); //驱动成功后进行连接
} catch (SQLException e) {
e.printStackTrace();
} catch (Exception e) {
e.printStackTrace();
}
return conn; //返回一个连接
}
public List<UserModel> selectAll() {
List<UserModel> uselist = new ArrayList<UserModel>();
try {
Connection conn = getconn();
Statement st = conn.createStatement();
//创建sql查询语句
String querySQL = "SELECT * FROM \"C##CCTEST\".T_USER";
//执行sql语句并且换回一个查询的结果集
ResultSet rs = st.executeQuery(querySQL);
while (rs.next()) { //循环遍历结果集
UserModel use = new UserModel();
int id = rs.getInt("id");
String userid = rs.getString("userid");
use.setID(id);
use.setUSERID(userid);
uselist.add(use);
}
st.close();
conn.close();
} catch (Exception e) {
e.printStackTrace();
}
return uselist;
}
第三、Mapper的方式：
2】resources下面UserMapper.xml写入下面内容，mapper namespace=对应的是java下面UserMapper的绝对路径。resultMap type=对应java下面User对象的绝对路径。User对象与数据库中一致。
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.pamirs.agent.httptest.demo.mapper.UserMapper">
<resultMap id="BaseResultMap" type="com.pamirs.agent.httptest.demo.entity.User">
<id column="id" jdbcType="BIGINT" property="id"/>
<result column="name" jdbcType="VARCHAR" property="name"/>
<result column="description" jdbcType="VARCHAR" property="description"/>
</resultMap>
<select id="selectByPrimaryKey" parameterType="java.lang.Long" resultMap="BaseResultMap">
select
<include refid="Base_Column_List"/>
from user where id = #{id,jdbcType=BIGINT}
</select>
</mapper>
3】java的UserMapper下面方法与resources的id名称一致。
@Component
public interface UserMapper {
User selectByPrimaryKey(Long id);
}
4】创建方法，调用UserMapper
@Resource
private UserMapper userMapper;
@Override
public User getUserById(Long id) {
return userMapper.selectByPrimaryKey(id);
}
5】在application.properties中添加mybatis.typeAliasesPackage和mybatis.mapperLocations，typeAliasesPackage=java下面mapper的绝对路径。
mybatis.mapperLocations=classpath*:/mapper/*.xml
mybatis.typeAliasesPackage=com.pamirs.agent.httptest.demo
6】在启动类里面添加启动项，MapperScan对应java下面mapper的绝对路径。
@SpringBootApplication
@MapperScan("com.pamirs.agent.httptest.demo.mapper")
public class HttpApplication {
public static void main(String[] args) {
SpringApplication.run(HttpApplication.class, new String[]{"--spring.profiles.active=c"});
}
}
网友评论