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>