登录 注册

学习笔记 -- 数据库开发

本贴最后更新于 2778 天前，其中的信息可能已经时移世异

//JDBC 基础

JDBC URL

jdbc:

jdbc:oracle:thin:@::database

jdbc:microsoft::; 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:, 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: //开启游标

利用 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="com.mysql.jdbc.Driver" />
				<property name="url" value="jdbc:mysql://localhost:3306/test" />
				<property name="username" value="root" />
				<property name="password" value="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();

其他操作，增删查改：

select user_id, userName, password from user where user_id =#{user_id}

insert into user (userName, password) values(#{userName}, #{password})

update user set userName = #{userName}, password = #{password} where user_id = #{user_id}

delete from user where user_id = #{user_id}

使用实例：

    //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 小时

  • 数据库

    据说 99% 的性能瓶颈都在数据库。

    343 引用 • 723 回帖
  • MySQL

    MySQL 是一个关系型数据库管理系统，由瑞典 MySQL AB 公司开发，目前属于 Oracle 公司。MySQL 是最流行的关系型数据库管理系统之一。

    692 引用 • 535 回帖
1 收藏
441 4 77 116 18 35 18 165

相关帖子

回帖
学习笔记 -- 数据库开发

欢迎来到这里！

我们正在构建一个小众社区，大家在这里相互信任，以平等 • 自由 • 奔放的价值观进行分享交流。最终，希望大家能够找到与自己志同道合的伙伴，共同成长。

注册 关于
请输入回帖内容 ...
KioLuo
回帖
0
 帖子
12
 积分
505

近期热议

推荐标签 标签

  • Caddy

    Caddy 是一款默认自动启用 HTTPS 的 HTTP/2 Web 服务器。

    12 引用 • 54 回帖 • 159 关注
  • Redis

    Redis 是一个开源的使用 ANSI C 语言编写、支持网络、可基于内存亦可持久化的日志型、Key-Value 数据库，并提供多种语言的 API。从 2010 年 3 月 15 日起，Redis 的开发工作由 VMware 主持。从 2013 年 5 月开始，Redis 的开发由 Pivotal 赞助。

    286 引用 • 248 回帖 • 44 关注
  • Hexo

    Hexo 是一款快速、简洁且高效的博客框架，使用 Node.js 编写。

    21 引用 • 140 回帖 • 3 关注
  • 知乎

    知乎是网络问答社区，连接各行各业的用户。用户分享着彼此的知识、经验和见解，为中文互联网源源不断地提供多种多样的信息。

    10 引用 • 66 回帖 • 1 关注
  • jQuery

    jQuery 是一套跨浏览器的 JavaScript 库，强化 HTML 与 JavaScript 之间的操作。由 John Resig 在 2006 年 1 月的 BarCamp NYC 上释出第一个版本。全球约有 28% 的网站使用 jQuery，是非常受欢迎的 JavaScript 库。

    63 引用 • 134 回帖 • 724 关注
  • Ruby

    Ruby 是一种开源的面向对象程序设计的服务器端脚本语言，在 20 世纪 90 年代中期由日本的松本行弘（まつもとゆきひろ/Yukihiro Matsumoto）设计并开发。在 Ruby 社区，松本也被称为马茨（Matz）。

    7 引用 • 31 回帖 • 216 关注
  • 旅游

    希望你我能在旅途中找到人生的下一站。

    93 引用 • 899 回帖 • 1 关注
  • Postman

    Postman 是一款简单好用的 HTTP API 调试工具。

    4 引用 • 3 回帖 • 9 关注
  • Scala

    Scala 是一门多范式的编程语言，集成面向对象编程和函数式编程的各种特性。

    13 引用 • 11 回帖 • 138 关注
  • MongoDB

    MongoDB（来自于英文单词“Humongous”，中文含义为“庞大”）是一个基于分布式文件存储的数据库，由 C++ 语言编写。旨在为应用提供可扩展的高性能数据存储解决方案。MongoDB 是一个介于关系数据库和非关系数据库之间的产品，是非关系数据库当中功能最丰富，最像关系数据库的。它支持的数据结构非常松散，是类似 JSON 的 BSON 格式，因此可以存储比较复杂的数据类型。

    90 引用 • 59 回帖 • 4 关注
  • jsoup

    jsoup 是一款 Java 的 HTML 解析器，可直接解析某个 URL 地址、HTML 文本内容。它提供了一套非常省力的 API，可通过 DOM，CSS 以及类似于 jQuery 的操作方法来取出和操作数据。

    6 引用 • 1 回帖 • 483 关注
  • Shell

    Shell 脚本与 Windows/Dos 下的批处理相似，也就是用各类命令预先放入到一个文件中，方便一次性执行的一个程序文件，主要是方便管理员进行设置或者管理用的。但是它比 Windows 下的批处理更强大，比用其他编程程序编辑的程序效率更高，因为它使用了 Linux/Unix 下的命令。

    123 引用 • 74 回帖 • 1 关注
  • Gitea

    Gitea 是一个开源社区驱动的轻量级代码托管解决方案，后端采用 Go 编写，采用 MIT 许可证。

    4 引用 • 16 回帖
  • Webswing

    Webswing 是一个能将任何 Swing 应用通过纯 HTML5 运行在浏览器中的 Web 服务器，详细介绍请看 将 Java Swing 应用变成 Web 应用 。

    1 引用 • 15 回帖 • 637 关注
  • Mobi.css

    Mobi.css is a lightweight, flexible CSS framework that focus on mobile.

    1 引用 • 6 回帖 • 745 关注
  • Q&A

    提问之前请先看《提问的智慧》，好的问题比好的答案更有价值。

    8454 引用 • 38507 回帖 • 155 关注
  • 单点登录

    单点登录（Single Sign On）是目前比较流行的企业业务整合的解决方案之一。SSO 的定义是在多个应用系统中，用户只需要登录一次就可以访问所有相互信任的应用系统。

    9 引用 • 25 回帖
  • WebComponents

    Web Components 是 W3C 定义的标准，它给了前端开发者扩展浏览器标签的能力，可以方便地定制可复用组件，更好的进行模块化开发，解放了前端开发者的生产力。

    1 引用 • 3 关注
  • Quicker

    Quicker 您的指尖工具箱！操作更少，收获更多！

    34 引用 • 148 回帖 • 1 关注
  • 星云链

    星云链是一个开源公链，业内简单的将其称为区块链上的谷歌。其实它不仅仅是区块链搜索引擎，一个公链的所有功能，它基本都有，比如你可以用它来开发部署你的去中心化的 APP，你可以在上面编写智能合约，发送交易等等。3 分钟快速接入星云链 (NAS) 测试网

    3 引用 • 16 回帖 • 6 关注
  • gRpc
    11 引用 • 9 回帖 • 70 关注
  • Notion

    Notion - The all-in-one workspace for your notes, tasks, wikis, and databases.

    7 引用 • 40 回帖
  • NetBeans

    NetBeans 是一个始于 1997 年的 Xelfi 计划，本身是捷克布拉格查理大学的数学及物理学院的学生计划。此计划延伸而成立了一家公司进而发展这个商用版本的 NetBeans IDE，直到 1999 年 Sun 买下此公司。Sun 于次年（2000 年）六月将 NetBeans IDE 开源，直到现在 NetBeans 的社群依然持续增长。

    78 引用 • 102 回帖 • 684 关注
  • PostgreSQL

    PostgreSQL 是一款功能强大的企业级数据库系统，在 BSD 开源许可证下发布。

    22 引用 • 22 回帖 • 2 关注
  • Thymeleaf

    Thymeleaf 是一款用于渲染 XML/XHTML/HTML5 内容的模板引擎。类似 Velocity、 FreeMarker 等，它也可以轻易的与 Spring 等 Web 框架进行集成作为 Web 应用的模板引擎。与其它模板引擎相比，Thymeleaf 最大的特点是能够直接在浏览器中打开并正确显示模板页面，而不需要启动整个 Web 应用。

    11 引用 • 19 回帖 • 364 关注
  • Log4j

    Log4j 是 Apache 开源的一款使用广泛的 Java 日志组件。

    20 引用 • 18 回帖 • 30 关注
  • 996

    “工作 996， 生病 ICU”

    13 引用 • 200 回帖 • 11 关注

最新标签