首页 > 代码库 > 学习笔记--数据库开发

学习笔记--数据库开发

//JDBC基础
JDBC URL
jdbc:mysql://<ip>:<port>/database
jdbc:oracle:thin:@<ip>:<port>:database
jdbc:microsoft:sqlserver://<ip>:<port>; DatabaseName=database

常用方法:
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery("select userName from user“);
ResultSet常用方法:
.next();
.previous();
.absolute();
.beforeFirst();
.afterLast();
.getString(ColumnName/Index);
.getInt(ColumnName/Index);
.getObject(ColumnName/Index);

构建步骤:
1. 装载驱动程序
Class.forName("com.mysql.jdbc.Driver");
2. 建立数据库连接
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:1234/database", user, password);
3. 执行SQL语句
Statement stmt = conn.createStatement();           //java.sql包
ResultSet rs = stmt.executeQuery("select userName from user");
4. 获取执行结果
while (rs.next()) {
    System.out.println("Hello " + rs.getString("userName"));
}
5. 清理环境
conn.close();         //一般需判断不为null,需要放在异常处理的finally中
stmt.close();
rs.close();

//JDBC进阶
游标提供一种客户端读取部分服务器端结果集的机制
DB_URL = jdbc:mysql://<ip>:<port>/database?useCursorFetch=true             //开启游标
利用PreparedStatement
.setFetchSize(1);

对于大对象的读取,采用流方式
ResultSet.getBinaryStream("columnName")

一次性往数据库中插入大量数据,采用批处理
Statement
.addBatch()
.executeBatch()
.clearBatch()

字符集编码
mysql语句:
show variables like ‘%character%‘;        //显示字符集编码信息,优先级别:column>table>database>server
show create table user;
JDBC设置:
DB_URL = DB_URL + characterEncoding=utf8

//数据库连接池
DBCP连接池,Tomcat的连接池组件,由三个jar包组成:
commons-dbcp.jar
commons-pool.jar
commons-logging.jar

创建BasicDataSource对象:
        public static BasicDataSource ds = null;
        ds = new BasicDataSource();
        ds.setUrl(DB_URL);
        ds.setDriverClassName(JDBC_DRIVER);
        ds.setUsername(USER);
        ds.setPassword(PASSWORD);
创建连接:
            conn = ds.getConnection();
            ...同JDBC基础

DBCP高级配置
BasicDataSource
.setInitialSize()          //设置初始连接数
.setMaxTotal()         //设置最大连接数
.setMaxWaitMillis()        //设置最大等待时间
.setMaxIdle()         //设置最大空闲连接数
.setMinIdle()        //设置最小空闲连接数,一般设置与最大空闲相同

DBCP定期检查
BasicDataSource
.setTestWhiteIdle(true)          //开启检查线程是否为有效的功能
.setMinEvictableIdleTimeMillis()     //设置关闭数据库连接的空闲时间阈值
.setTimeBetweenEvictionRunMillis()      //设置检查运行时间的间隔,一般小于默认最小空闲连接时间8小时

//SQL注入与防范
SQL注入例子:
在SQL语句拼接中,userName = "zhangsan‘;#",欺骗服务器

SQL注入防范:
使用PreparedStatement:
            PreparedStatement ptmt = null;
            ptmt = conn.prepareStatement("select * from user where userName = ? and password = ?");
            ptmt.setString(1, userName);
            ptmt.setString(2, password);
            rs = ptmt.executeQuery();

//事务
JDBC事务控制
Connection
.setAutoCommit(false)             //开启事务,默认为true即未开启事务,设置为false为开启事务
.commit()              //提交事务
.rollback()                //回滚事务

检查点
Savepoint sp = conn.setSavepoint();         //设置检查点
conn.rollback(sp);                    //回滚到检查点状态

脏读,不可重复读,幻读

事务并发执行
事务隔离级别:
读未提交(read uncommitted)
读提交 (read committed)
重复读(repeatable read)
串行化(serializable)
默认级别为重复读,级别越高,数据库性能越差
设置隔离级别:
Connection
.getTransactionIsolation()
.setTransactionIsolation()

死锁
死锁的必要条件:
互斥,请求和保持,不剥夺,环路等待
外部加锁:
共享锁(S):select * from table lock in share mode
排他锁(X):select * from table for update
内部加锁:
数据库系统为实现ACID特性自动添加
快照读:支持不加锁,select * from table为快照读
当前读:可能加锁
解决死锁方法:强制回滚代价小的事务
分析死锁方法:show engine innodb status

//Mybatis
ORM框架
SqlSessionFactory配置,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="http://www.mamicode.com/com.mysql.jdbc.Driver" />
                <property name="url" value="http://www.mamicode.com/jdbc:mysql://localhost:3306/test" />
                <property name="username" value="http://www.mamicode.com/root" />
                <property name="password" value="http://www.mamicode.com/qiyao.luo" />
            </dataSource>
        </environment>
    </environments>
</configuration>

创建Java对象与SQL语句映射关系配置文件,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.micro.profession.mybatis.GetUserInfo">
    <!-- 在select标签中编写查询的SQL语句,设置select标签的id属性为getUser,
    resultType="MyBatisTest.User"就表示将查询结果封装成一个User类 -->
    <!-- 根据id查询得到一个user对象 -->
<select id="getUser" parameterType="int" resultType="com.micro.profession.mybatis.User">
select id, userName, corp from user where id =#{id}
</select>
</mapper>

注册配置文件
在SqlSessionFactory的配置文件中加入
<mappers>
   <mapper resource="com/micro/profession/mybatis/userMapper.xml" />
</mappers>

Mybatis使用步骤:
        //1. 声明配置文件的目录
        String resource = "conf.xml";
        //2. 加载应用配置文件
        InputStream is = HelloMybatis.class.getClassLoader().getResourceAsStream(resource);
        //3. 创建SqlSessionFactory
        SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(is);
        //4. 获取session
        SqlSession session = sessionFactory.openSession();
        //5. 获取操作类
        GetUserInfo getUserInfo = session.getMapper(GetUserInfo.class);
        User user = getUserInfo.getUser(2);
        System.out.println(user.getId() + " " + user.getUserName() + " " + user.getPassword());
        session.close();

采用注解的方法进行映射:
    @Select("select * from user where user_id = #{id}")
    public User getUser(int id);

使用方法:
在原来使用步骤基础上插入:
        SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(is); 
        Configuration conf = sessionFactory.getConfiguration();
        conf.addMapper(GetUserInfoAnnotation.class);
        SqlSession session = sessionFactory.openSession();  

其他操作,增删查改:
<?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.netease.mybatis.practice.GetUserInfo">
<select id="getUser" parameterType="int" resultType="com.netease.mybatis.practice.User">
select user_id, userName, password from user where user_id =#{user_id}
</select>
<insert id="addUser" parameterType="com.netease.mybatis.practice.User" useGeneratedKeys="true" keyProperty="user_id">
insert into user (userName, password) values(#{userName}, #{password})
</insert>
<update id="updateUser" parameterType="com.netease.mybatis.practice.User">
update user set userName = #{userName}, password = #{password} where user_id = #{user_id}
</update>
<delete id="deleteUser" parameterType="com.netease.mybatis.practice.User">
delete from user where user_id = #{user_id}
</delete>
</mapper>

使用实例:
        //1. 声明配置文件的目录
        String resource = "conf.xml";
        //2. 加载应用配置文件
        InputStream is = HelloMybatis.class.getClassLoader().getResourceAsStream(resource);
        //3. 创建SqlSessionFactory
        SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(is);
        //4. 获取session
        SqlSession session = sessionFactory.openSession(true);
        //5. 获取操作类
        GetUserInfo getUserInfo = session.getMapper(GetUserInfo.class);
        //插入用户
        User user = new User("xiaoming""123456");
        getUserInfo.addUser(user);
        System.out.println(user.getId());
        //查询用户
        user = getUserInfo.getUser(user.getId());
        System.out.println(user.getId() + " " + user.getUserName() + " " + user.getPassword());
        //更改用户
        user.setUserName("LiMing");
        getUserInfo.updateUser(user);
        //删除用户
        getUserInfo.deleteUser(user);
        //关闭session
        session.close();

Mybatis传递多个参数的方法
DAO层函数
Public User selectUser(@param(“userName”)String name,@param(“userArea”)String area);
对应Mapper
<select id=" selectUser" resultMap="BaseResultMap">
   select  from user_user_t   where user_name = #{userName,jdbcType=VARCHAR} and user_area=#{userArea,jdbcType=VARCHAR}
</select

复杂查询,使用ResultMap,实现复杂查询结果到复杂对象关联关系的转化
Constructor
类在实例化时,用来注入结果到构造方法中
idArg —— ID参数;标记结果作为ID可以帮助提高整体性能
arg —— 注入到构造方法的一个普通结果
Collection
实现一对多的关联
id —— 一个ID结果;标记结果作为ID可以帮助提高整体性能
result —— 注入到字段或JavaBean属性的普通结果
Mapper的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.netease.mybatis.practice.UserOp">
<select id="getUser" parameterType="int" resultMap="UserMap">
select u.user_id as user_id, userName, password, c.id as courseId, courseName from user u left
    join
    UserCourse uc on u.user_id = uc.user_id left
    join
    course c on c.id = uc.course_id where u.user_id =#{user_id}
</select>
<resultMap id="UserMap" type="com.netease.mybatis.practice.User">
   <constructor>
   <idArg column="user_id" javaType="int" />
<arg column="userName" javaType="String" />
<arg column="password" javaType="String" />
</constructor>
<collection property="courses" ofType="com.netease.mybatis.practice.Course">
   <id property="courseId" column="courseId" />
<result property="courseName" column="courseName" />
</collection>
</resultMap>
</mapper>
collection项若为Integer,String等类型,可设为
      <collection property="list" column="productname" ofType="java.lang.String">
         <result column="productname" />
      </collection>

Association,实现复杂类型之间的关联
id —— 一个ID结果;标记结果作为ID可以帮助提高整体性能
result —— 注入到字段或JavaBean属性的普通结果
xml文件内容中增加association项,修改为:
<collection property="courses" ofType="com.netease.mybatis.practice.Course">
   <id property="courseId" column="courseId" />
<result property="courseName" column="courseName" />
<association property="teacher" column="teacherId" javaType="com.netease.mybatis.practice.Teacher">
   <id property="teacherId" column="teacherId" />
<result property="teacherName" column="teacherName" />
</association>
</collection>

Mybatis自带连接池
连接池常用配置选项
poolMaximumActiveConnections:
数据库最大活跃连接数,不宜设置过大
poolMaximumIdleConnections:
最大空闲连接数,一般设置与poolMaximum相同即可
poolMaximumCheckoutTime:
获取连接时如果没有idleConnection同时activeConnection达到最大值,则从activeConnection列表第一个连接开始检查,如果超过此时间,则使其失效,返回该连接,建议设置为预期最大SQL执行时间
poolTimeToWait:
获取服务器连接的超时时间,如果超时,打印日志重新获取连接。默认为20s
poolPingEnabled:
启用连接侦测,检查连接池中的连接是否为有效连接,默认为关闭,建议开启
poolPingQuery:
侦测SQL,建议使用select 1,开销小
poolPingConnectionsNotUsedFor:
侦测时间,建议小于服务器端超时时间,MySQL默认超时8小时



学习笔记--数据库开发