2.用Mybatis框架来连接数据库

　　在原JDBC基础上，进一步简化开发代码，只需实体类（根据表）、接口（CRUD操作）、mybatis.cfg.xml(链接数据库的配置文件)、xml（sql语句）打完收工。

　　重点看下来给两个xml文件

mybatis.cfg.xml(链接数据库的配置文件)：

　　这里列举两个，一个链接Oracle，取Bank表：一个链接MySQL，取Userinfos表

<?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>
    <!--给实体类起别名-->
    <typeAliases>
        <typeAlias type="com.zyp.mb.entity.Bank" alias="bank"></typeAlias>
        <typeAlias type="com.zyp.mb.entity.Userinfos" alias="user"></typeAlias>
    </typeAliases>
    <environments default="zyporacle">  默认环境
        <environment id="zyporacle">
            <!--数据库事务JDBC-->
            <transactionManager type="JDBC"></transactionManager>
            <!--数据库链接驱动-->
            <dataSource type="POOLED">
                <property name="driver" value="oracle.jdbc.driver.OracleDriver"/>
                <property name="url" value="jdbc:oracle:thin:@192.168.234.151:1521:orcl"/>
                <property name="username" value="zyp"/>
                <property name="password" value="ok"/>
            </dataSource>
        </environment>
        <environment id="zypmysql">
            <transactionManager type="JDBC"></transactionManager>
            <dataSource type="POOLED">
                <property name="driver" value="com.mysql.jdbc.Driver"/>
                <property name="url" value="jdbc:mysql://192.168.234.150:3306/mydemo"/>
                <property name="username" value="root"/>
                <property name="password" value="ok"/>
            </dataSource>
        </environment>
    </environments>
    <mappers>
        <mapper resource="mapper/bank.xml"></mapper>
        <mapper resource="mapper/userinfos.xml"></mapper>
    </mappers>
</configuration>

 

xml（sql语句）：

bank.xml:

<?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.zyp.mb.dao.BankDAO">
    <insert id="insertCard" parameterType="bank">
        insert into bank values(#{id},#{name},#{money})
    </insert>
    <!--动态sql-->
    <insert id="insertBatch">
        insert into bank
        <foreach collection="list" item="bk" separator="union all">
            select #{bk.id},#{bk.name},#{bk.money} from dual
        </foreach>
        sql+="select #{bk.id},#{bk.name},#{bk.money} from dual"+"union all"
        }
    </insert>

    <!--不定条件查询-->
    <select id="findAll" resultType="bank" parameterType="bank">
        select * from bank
        <where>
            <if test="id != 0">
                id=#{id}
            </if>
            <if test="name != null">
                and name = #{name}
            </if>
            <if test="money != 0">
                and money >#{money}
            </if>
        </where>
    </select>
</mapper>

 

<?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.zyp.mb.dao.UserinfosDAO">
    <select id="findUserTop10" resultType="user">
        select * from userinfos limit 10
    </select>
</mapper>

 

interrface:

public interface BankDAO {
    public List<Bank> findAll(Bank bank);//不定条件查询 多条件查询
    public void insertCard(Bank bank);//插入一条数据
    public void insertBatch(List<Bank> bks);//批量插入
}

public interface UserinfosDAO {
    public List<Userinfos> findUserTop10();
}

 

主程序：

    public static void main(String[] args) throws IOException {
//        读取配置文件
        Reader reader = Resources.getResourceAsReader("mybatis.cfg.xml");
//        使用配置文件生成一个SqlSessionFactory--->产生链接的工厂  并指定环境，否则走默认
        SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(reader,"zypmysql");
//        使用工厂获取一个链接
        SqlSession session = factory.openSession();//如果传参true进去,每一句都自动提交
        UserinfosDAO udao = session.getMapper(UserinfosDAO.class);
        System.out.println(udao.findUserTop10());
    }
}

    public static void main(String[] args) throws IOException {
//        读取配置文件
        Reader reader = Resources.getResourceAsReader("mybatis.cfg.xml");
//        使用配置文件生成一个SqlSessionFactory--->产生链接的工厂
        SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(reader,"zyporacle");//如果不指定环境，走默认环境
//        使用工厂获取一个链接
        SqlSession session = factory.openSession();//如果传参true进去,每一句都自动提交
        BankDAO bdao = session.getMapper(BankDAO.class);

        //找名字为ww的
        System.out.println(bdao.findAll(new Bank(0, "ww", 0)));
        //找id为4的
        System.out.println(bdao.findAll(new Bank(4, null, 0)));
        //找工资大于3000的
        System.out.println(bdao.findAll(new Bank(0, null, 3000)));
        //找所有
        System.out.println(bdao.findAll(new Bank(0, null, 0)));

    }
}