JAVA的连接数据库JDBC 和 Mybatis的DAO例子

//JDBC
 1 import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public class DbUtil {

    public static final String URL = "jdbc:mysql://localhost:3306/imooc";
    public static final String USER = "liulx";
    public static final String PASSWORD = "123456";

    public static void main(String[] args) throws Exception {
        //1.加载驱动程序
        Class.forName("com.mysql.jdbc.Driver");
        //2. 获得数据库连接
        Connection conn = DriverManager.getConnection(URL, USER, PASSWORD);
        //3.操作数据库，实现增删改查
        Statement stmt = conn.createStatement();
        ResultSet rs = stmt.executeQuery("SELECT user_name, age FROM imooc_goddess");
        //如果有数据，rs.next()返回true
        while(rs.next()){
            System.out.println(rs.getString("user_name")+" 年龄："+rs.getInt("age"));
        }
    }
}

 

mybatis的DAO实现

//DAO层，接口
package com.DAO;

import com.po.Word;

import java.util.List;

public interface WordDAO {
    //根据number查询单词的信息
    public Word findWordByNumber(int number) throws Exception;
    //添加单词
    public void insertWord(String word) throws Exception;
    //删除单词信息
    public void deleteWord(int number) throws Exception;
    //返回数组呢
    public List<Word> findNumberByWord(String word) throws Exception;
}

//DAO接口的实现类Impl
package com.DAO;

import com.mysql.cj.x.protobuf.MysqlxNotice;
import com.po.Word;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;

import java.util.ArrayList;
import java.util.List;

public class WordDaoImpl implements WordDAO {
    private SqlSessionFactory sqlSessionFactory;

    public WordDaoImpl(SqlSessionFactory sqlSessionFactory) {
        this.sqlSessionFactory = sqlSessionFactory;
    }

    @Override
    public Word findWordByNumber(int number) throws Exception {
        SqlSession sqlSession = sqlSessionFactory.openSession();
        Word w = sqlSession.selectOne("test.findWordByNumber", number);
        sqlSession.close();
        return w;
    }

    @Override
    public void insertWord(String word) throws Exception {
        SqlSession sqlSession = sqlSessionFactory.openSession();
        Word w = new Word();
        w.setUnit(1);
        w.setMeans_adj("ad");
        w.setNumber(122);
        w.setWord("adadad");

        sqlSession.insert("test.insertWord", w);
        sqlSession.close();
    }

    @Override
    public void deleteWord(int number) throws Exception {
        SqlSession sqlSession = sqlSessionFactory.openSession();
        sqlSession.delete("test.deleteWord", number);
        sqlSession.close();
    }

    @Override
    public List<Word> findNumberByWord(String word) throws Exception {
        SqlSession sqlSession = sqlSessionFactory.openSession();
        List<Word> list = sqlSession.selectList("test.findWordByWord", word);
        sqlSession.close();
        return list;
    }
}

<!--配置文件 Word.xml -->
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper
        PUBLIC "..//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="test">
    <select id="findWordByNumber" parameterType="int" resultType="com.po.Word">
        SELECT * FROM xindongfangkaoyan2021 WHERE number = #{number};
    </select>

    <select id="findWordByWord" parameterType="String" resultType="com.po.Word">
        SELECT * FROM xindongfangkaoyan2021 WHERE word LIKE #{word};
    </select>

    <insert id="insertWord" parameterType="com.po.Word">
        INSERT INTO xindongfangkaoyan2021(unit,word,means_n,means_adj)
        VALUE(#{unit},#{word},#{means_n},#{means_adj})
        <selectKey keyProperty="number" order="AFTER" resultType="java.lang.Integer" >
            Select LAST_INSERT_ID();
        </selectKey>
    </insert>
    
    <delete id="deleteWord" parameterType="String">
        delete from xindongfangkaoyan2021 where word = #{word};
    </delete>

    <select id="findNumberByWord" parameterType="String" resultType="com.po.Word">
        SELECT * FROM xindongfangkaoyan2021 WHERE word = #{word};
    </select>

    <update id="update" parameterType="com.po.Word">
        UPDATE xindongfangkaoyan2021 SET unit = #{unit} WHERE word = #{word};
    </update>
</mapper>

<!--配置文件SqlMapConfig.xml-->
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE configuration
        PUBLIC "..//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <property name="driver" value="com.mysql.cj.jdbc.Driver"/>
                <property name="url" value="jdbc:mysql://localhost/xxxx?characterEncoding=utf-8&amp;serverTimezone=UTC&amp;useSSL=true"/>
                <property name="username" value="xxxxxx"/>
                <property name="password" value="xxxxxx"/>
            </dataSource>
        </environment>
    </environments>
    <mappers>
        <mapper resource="sqlmap/Word.xml"/>
    </mappers>
</configuration>

//测试类 test，测试了findNumberByWord

import com.DAO.WordDAO;
import com.DAO.WordDaoImpl;
import com.po.Word;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import java.io.InputStream;
import java.util.List;
import java.util.Scanner;

public class test {
    public static SqlSessionFactory sqlSessionFactory;

    public static void main(String[] args) throws Exception {
        test();
        System.out.println("输出完成！");
    }

    public static void test() throws Exception {
        System.out.println("请输入：");
        Scanner sc =new Scanner(System.in);
        String s = sc.nextLine();

        String resource = "SqlMapConfig.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        WordDAO w = new WordDaoImpl(sqlSessionFactory);

        List<Word> rarely = w.findNumberByWord(s);
        for(Word word : rarely){
            System.out.println(word.toString());
        }
    }
}

 

以上----------------------------2021/4/21第一次编辑