此功能用于系统集成多数据源的管理和查询。可管理多个数据源，根据对应的配置进行储存数据库对应的数据、便于管理和查询。
应用场景、表单的配置选取数据源，选择表格后查询出表格对应的字段名称，长度，字段的属性。
1、先配置pom.xml
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
</dependency>
<dependency>
<groupId>commons-dbutils</groupId>
<artifactId>commons-dbutils</artifactId>
<version>1.7</version>
</dependency>
<dependency>
<groupId>com.mchange</groupId>
<artifactId>c3p0</artifactId>
<version>0.9.5.2</version>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<optional>true</optional>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
2、新建数据库来管理需要储存的数据库信息。新建实体类实现类进行增删改查，这里就不详细进行叙述了。
3、新建jdbcDome来储存驱动，连接的URL，账号，密码。
@Data
@EqualsAndHashCode(callSuper = false)
@Accessors(chain = true)
public class jdbcDome {
private String driverClassName;//驱动
private String jdbcUrl;//数据库URL
private String userName;//数据库账号
private String passWord;//数据库密码
}
4、新建JDBCUtils来管理连接
import com.hsdev.biz.qu.entity.jdbcDome;
import java.sql.Connection;
import java.sql.DriverManager;
import java.util.Map;
public class JDBCUtils {
public static Connection getConnection(jdbcDome jdbc) throws Exception{
Connection connection = null;
//Class.forName("com.mysql.cj.jdbc.Driver");
//此处是写死的MySql的驱动。也可根据配置进行配置。
DriverManager.registerDriver(new com.mysql.cj.jdbc.Driver());
connection = DriverManager.getConnection(jdbc.getJdbcUrl(), jdbc.getUserName(), jdbc.getPassWord());
return connection;
}
}
5、新建DBUtilsController方法进行查询返回值的设置
import com.hsdev.biz.qu.entity.SysDataSource;
import com.hsdev.biz.qu.entity.jdbcDome;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
public class DBUtilsController {
/**
*
* @return
* @throws Exception
*/
//查询
public static List<Map<String,Object>> queryAllStudent(String sql,
SysDataSource sysDataSource) throws Exception{
//1. 获取连接对象
jdbcDome jdbc = new jdbcDome();
jdbc.setDriverClassName(sysDataSource.getDbDriver());
jdbc.setJdbcUrl(sysDataSource.getDbUrl());
jdbc.setUserName(sysDataSource.getDbUserName());
jdbc.setPassWord(sysDataSource.getDbPassword());
Connection connection = JDBCUtils.getConnection(jdbc);
//2.创建执行对象
PreparedStatement pstmt = connection.prepareStatement(sql);
//3.执行SQL查询，返回结果集对象
ResultSet rs = pstmt.executeQuery();
//4.循环处理结果集中的每一条数据
List<Map<String,Object>> list = new ArrayList<>();
ResultSetMetaData md = rs.getMetaData();//获取键名
int columnCount = md.getColumnCount();//获取行的数量
while(rs.next()){ //rs.next()的作用，1. 返回是否有下一条数据， 2.将游标指向下一行
Map<String,Object> map = new HashMap<>();
for (int i = 1; i <= columnCount; i++) {
map.put(md.getColumnName(i), rs.getObject(i));//获取键名及值
}
list.add(map);
/*Map<String,Object> map = new HashMap<>();
String name = rs.getString("name");
String comment = rs.getString("comment");
int rows = Integer.parseInt(rs.getString("rows"));
map.put("name",name);
map.put("comment",comment);
map.put("rows",rows);
list.add(map);*/
}
while(rs.next()){ //rs.next()的作用，1. 返回是否有下一条数据， 2.将游标指向下一行
Map<String,Object> map = new HashMap<>();
String name = rs.getString("name");
String comment = rs.getString("comment");
int rows = Integer.parseInt(rs.getString("rows"));
map.put("name",name);
map.put("comment",comment);
map.put("rows",rows);
list.add(map);
}
//5.关闭连接对象
rs.close();
pstmt.close();
connection.close();
//6.返回结果集
return list;
}
public static List<Map<String, Object>> queryAllStudentTable(String sql,SysDataSource sysDataSource) throws Exception{
//1. 获取连接对象
jdbcDome jdbc = new jdbcDome();
jdbc.setDriverClassName(sysDataSource.getDbDriver());
jdbc.setJdbcUrl(sysDataSource.getDbUrl());
jdbc.setUserName(sysDataSource.getDbUserName());
jdbc.setPassWord(sysDataSource.getDbPassword());
Connection connection = JDBCUtils.getConnection(jdbc);
//2.创建执行对象
PreparedStatement pstmt = connection.prepareStatement(sql);
//3.执行SQL查询，返回结果集对象
ResultSet rs = pstmt.executeQuery();
//4.循环处理结果集中的每一条数据
List<Map<String,Object>> list = new ArrayList<>();
ResultSetMetaData md = rs.getMetaData();//获取键名
int columnCount = md.getColumnCount();//获取行的数量
while(rs.next()) { //rs.next()的作用，1. 返回是否有下一条数据， 2.将游标指向下一行
Map<String, Object> map = new HashMap<>();
for (int i = 1; i <= columnCount; i++) {
map.put(md.getColumnName(i), rs.getObject(i));//获取键名及值
}
list.add(map);
}
/*while(rs.next()){ //rs.next()的作用，1. 返回是否有下一条数据， 2.将游标指向下一行
Map<String,Object> map = new HashMap<>();
String name = rs.getString("name");
String comment = "";
String type = rs.getString("type");
int rows = 0;
if (rs.getString("comment")!=null && !"".equals(rs.getString("comment"))){
comment = rs.getString("comment");
}
if (rs.getString("rows")!=null && !"".equals(rs.getString("rows"))){
Integer.parseInt(rs.getString("rows"));
}
map.put("name",name);
map.put("comment",comment);
map.put("type",type);
map.put("rows",rows);
list.add(map);
}*/
//5.关闭连接对象
rs.close();
pstmt.close();
connection.close();
//6.返回结果集
return list;
}
/**********************************修改、删除************************/
/**
*
* @param
* @return
* @throws Exception
*/
//添加单个
/*public int addStudent(User user) throws Exception{
//1. 获取连接对象
Connection connection = JDBCUtils.getConnection();
//2.创建预编译对象
String sql = "insert into user (name, age, sex, height, weight) values (?,?,?,?,?)";
PreparedStatement pstmt = connection.prepareStatement(sql);
//3.设置占位符参数
pstmt.setString(1, user.getName());
pstmt.setInt(2, user.getAge());
pstmt.setString(3, user.getSex());
pstmt.setInt(4, user.getHeight());
pstmt.setInt(5, user.getWeight());
//4.执行更新
int effectRows = pstmt.executeUpdate();
//5.关闭连接对象
pstmt.close();
connection.close();
return effectRows;
}
//添加多个
public int insertStudentByBatch(List<User> user) throws Exception{
//1.创建连接对象
Connection connection = JDBCUtils.getConnection();
//2.创建预编译对象
String sql = "insert into student (name, age, sex, height, weight) values (?, ?, ?, ?, ?)";
PreparedStatement pstmt = connection.prepareStatement(sql);
//3.设置占位符参数
for(User stu : user){
pstmt.setString(1, stu.getName());
pstmt.setInt(2, stu.getAge());
pstmt.setString(3, stu.getSex());
pstmt.setInt(4, stu.getHeight());
pstmt.setInt(5, stu.getWeight());
pstmt.addBatch();
}
//４.执行更新对象
int[] ints = pstmt.executeBatch();
int effectRows = 0;
for(int rows : ints){
effectRows += rows;
}
//5.关闭连接
pstmt.close();
connection.close();
return effectRows;
}
//删除
public int deleteStudentByid(Integer id) throws Exception{
//1.获取连接对象
Connection connection = JDBCUtils.getConnection();
//2.创建执行对象
String sql = "delete from user where id = ?";
PreparedStatement pstmt = connection.prepareStatement(sql);
//3.设置占位符参数
pstmt.setInt(1, id);
//4.执行更新
int effectRows = pstmt.executeUpdate();
//5.关闭连接
pstmt.close();
connection.close();
return effectRows;
}
//按照学号更新学生信息
public int updateStudent(User user) throws Exception{
//1.创建连接对象
Connection connection = JDBCUtils.getConnection();
//2.创建执行对象
String sql = "update user set name = ?, age = ?, sex = ? where id = ?";
PreparedStatement pstmt = connection.prepareStatement(sql);
//3.设置占位符参数
pstmt.setString(1, user.getName());
pstmt.setInt(2, user.getAge());
pstmt.setString(3, user.getSex());
pstmt.setInt(4, user.getId());
//4.执行更新
int effectRows = pstmt.executeUpdate();
//5.关闭连接
pstmt.close();
connection.close();
return effectRows;
}*/
/***************************************************************************************/
}
6、5里面的SysDataSource方法为存储数据库的实体类。
@Data
@EqualsAndHashCode(callSuper = false)
@Accessors(chain = true)
@TableName("sys_datasource")
public class SysDataSource extends BaseEntity {
private static final long serialVersionUID = 1L;
private String name; // 连接中文名称
private String enname; //数据库用户名
private String dbPassword; // 数据库密码
private String dbUrl; // 数据库链接
private String dbDriver; // 数据库驱动类
}
7、设置查询条件。查询某一个数据源下面的所有表格。查询某个数据源下某个表格的字段名称等数据。
/**
* 查询表列表
* @return
*/
static List<Map<String,Object>> TableList(SysDataSource sysDataSource) throws Exception {
String sql = "SELECT TABLE_NAME name,TABLE_COMMENT comment,TABLE_ROWS rows FROM information_schema.tables WHERE TABLE_SCHEMA = '"+sysDataSource.getEnname()+"' ORDER BY TABLE_NAME";
return DBUtilsController.queryAllStudent(sql,sysDataSource);
}
/**
* 查询表列表下字段
* @return
*/
static List<Map<String,Object>> TableListEntity(SysDataSource sysDataSource,String table) throws Exception {
String sql = "SELECT column_NAME name,column_comment comment,column_type type ,(\n" +
" CASE\n" +
" WHEN data_type = 'float'\n" +
" OR data_type = 'double'\n" +
" OR data_type = 'TINYINT'\n" +
" OR data_type = 'SMALLINT'\n" +
" OR data_type = 'MEDIUMINT'\n" +
" OR data_type = 'INT'\n" +
" OR data_type = 'INTEGER'\n" +
" OR data_type = 'decimal'\n" +
" OR data_type = 'bigint'\n" +
" THEN\n" +
" NUMERIC_PRECISION\n" +
" ELSE\n" +
" CHARACTER_MAXIMUM_LENGTH\n" +
" END\n" +
" ) AS rows \n" +
"FROM INFORMATION_SCHEMA.Columns \n" +
"WHERE table_name='"+table+"' AND table_schema='"+sysDataSource.getEnname()+"'";
return DBUtilsController.queryAllStudentTable(sql,sysDataSource);
}
扫一扫
896
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
