java中使用sqlserver调用MySQL数据库的表进行操作

操作"/>

java中使用sqlserver调用MySQL数据库的表进行操作

项目场景：

部分需求需要对不同的数据库进行操作,我这里是一个小程序里调用了另一个数据

解决方案：

目前我个人使用了两个方案解决的,看自己觉得哪个方便

方案一: 使用JDBC

新建一个实体类,相当于mapper.xml了,然后在这个实体类里进行数据库的连接操作等

static String driver = "com.mysql.cj";
static String sqlServerUrl = "jdbc:mysql://localhost:1000/dts_shop";
static String username = "admin";
static String password = "admin";

这是你数据库的连接账号密码

这是连接成功之后调用数据库,进行sql操作,执行完sql之后查询的话就是这样需要遍历一下然后放入到集合返回回去

public static List<DtsCommentVO> selectDissatisfiedOrders(Integer id,Integer userId) {String sql = "SELECT * \n" +"FROM dts_comment AS a \n" +"LEFT JOIN dts_goods AS b ON a.value_id = b.id \n" +"WHERE a.type = 0 and a.user_id = "+userId+" and a.value_id = "+id+" ORDER BY a.add_time desc ";List<DtsCommentVO> dissatisfiedOrderNum = new ArrayList<>();try {Class.forName(driver);//获取配置文件默认数据库地址Connection conn = DriverManager.getConnection(sqlServerUrl, username, password);Statement st = conn.createStatement();ResultSet rs = st.executeQuery(sql);while (rs.next()) {int valueId = rs.getInt("value_id");int type = rs.getInt("type");String content = rs.getString("content");int userIds = rs.getInt("user_id");String picUrls = rs.getString("pic_urls");int star = rs.getInt("star");Date addTime = rs.getDate("add_time");Date updateTime = rs.getDate("update_time");int deleted = rs.getInt("deleted");DtsCommentVO dtsCommentVO = new DtsCommentVO();dtsCommentVO.setValueId(valueId);dtsCommentVO.setType(type);dtsCommentVO.setContent(content);dtsCommentVO.setUserId(userIds);dtsCommentVO.setPicUrls(picUrls);dtsCommentVO.setStar(star);dtsCommentVO.setAddTime(addTime);dtsCommentVO.setUpdateTime(updateTime);dtsCommentVO.setDeleted(deleted);dissatisfiedOrderNum.add(dtsCommentVO);}// 6.释放资源rs.close();st.close();conn.close();} catch (Exception e) {System.out.println("报错:  " + e);}return dissatisfiedOrderNum;
}

方案二:

使用mybatis

这里我只展示sql,因为前面的操作和我们平时写的没区别,sql的话就是使用selcet标签使用OPENQUERY 调用mysql的服务之后sql要放在单引号内执行,新增的话就这样写

<select id="select" resultType="com.pojo.Dts">INSERT OPENQUERY (MysqlServer, 'SELECT value_id,type,content,user_id,has_picture,pic_urls,star,add_time,update_time,deleted FROM dts_sh.dts_comm')VALUES (#{valueId}, #{type}, #{content}, #{userId}, #{hasPicture}, #{picUrls}, #{star}, #{addTime}, #{updateTime}, #{deleted});
</select>

查询的写法

取值的时候需要使用占位符就像这样"${id}"

SELECT * FROM OPENQUERY(MysqlServer, 'SELECT a.* FROM dts_comment AS a LEFT JOIN dts_goods AS b ON a.value_id = b.id WHERE a.type = 0 AND a.star>=4 AND a.value_id = "${id}"')

注:在调用其他数据库时别忘了加依赖,否则连接不上

根据需求和自己的版本号进行填写

 

<dependency><groupId>mysql</groupId><artifactId>mysql-connector-java</artifactId><version>8.0.23</version>
</dependency>