Spring中级课程第二课

一. sql

1. 数据库基本操作CRUD（关键字大小写都可以,尽量大写）
   插入INSERT INTO table_name(列1，列 2，。。。)VALUES(值1，值2，...)
   insert into user(name,password) values('a1','b1')
   选择 SELECT */列名 FROM 表名称
   SELECT id,name FROM toutiao.user 两列
   SELECT * FROM toutiao.user 整个表
   SELECT * FROM user WHERE id > 1 ORDER BY id desc LIMIT 2, 3; 以id降序，偏移2，选3个
   更新UPDATE 表名称 SET 列名称 = 新值 WHERE 列名称 = 某值
   update user set password = "aa1" where id =1;
   条件语句一般选主键域，或者唯一值的域
   删除 DELET FROM 表名称 WHERE 列名称 = 值
2. JDBC:java数据库连接
   基本操作流程：
   a. 加载驱动
   b. 创建数据库链接
   c. 创建Statement对象
   d. 执行SQL获取数据（使用mybatis，只需关注这一步)
   e. 数据转化
   f. 资源释放
3. mybatis配置
   ** mybatis 中文简介网站**
   逻辑过程：

• 应用需要和数据库交互，所以要配个数据库

#<!--application.properties文件-->
spring.datasource.url=jdbc:mysql://localhost:3306/toutiao?useUnicode=true&characterEncoding=utf8&useSSL=false
spring.datasource.username=root
spring.datasource.password=tangxueming
#不自动初始化
#spring.datasource.initialize=false
#如果启动web程序，需要在classpath中找
mybatis.config-location=classpath:mybatis-config.xml

• 引用mybatis，所以要把mybatis引入pom.xml,加入依赖后自动下载,然后mybatis又要数据库的连接，所以加入两个依赖

<!--插入到pom.xml文件的<dependencies></depebdebcies>中间-->
<dependency>
  <groupId>mysql</groupId>
  <artifactId>mysql-connector-java</artifactId>
  <scope>runtime</scope></dependency>
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
  <artifactId>mybatis-spring-boot-starter</artifactId>
  <version>1.1.1</version>
</dependency>

• mybatis配置文件加入到资源文件夹下

<?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>
    <settings>
        <!-- Globally enables or disables any caches configured in any mapper under this configuration -->
        <setting name="cacheEnabled" value="true"/>
        <!-- Sets the number of seconds the driver will wait for a response from the database -->
        <setting name="defaultStatementTimeout" value="3000"/>
        <!-- Enables automatic mapping from classic database column names A_COLUMN to camel case classic Java property names aColumn -->
        <setting name="mapUnderscoreToCamelCase" value="true"/>
        <!-- Allows JDBC support for generated keys. A compatible driver is required.
        This setting forces generated keys to be used if set to true,
         as some drivers deny compatibility but still work -->
        <setting name="useGeneratedKeys" value="true"/>
    </settings>
    <!-- Continue going here -->
</configuration>

• 测试：建4个文件，数据初始化sql文件，用户类文件，用户DAO接口，用户数据测试用例。路径：
  src/test/resources/init-schema.sql
  src/main/java/com.nowcoder.model/User.java
  src/main/java/com.nowcoder.dao/UserDAO.java
  src/test/java/com.nowcoder/InitDatabaseTests.java

//init-schema.sql
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` ( 
 `id` int(11) unsigned NOT NULL AUTO_INCREMENT, 
 `name` varchar(64) NOT NULL DEFAULT '',
  `password` varchar(128) NOT NULL DEFAULT '',
  `salt` varchar(32) NOT NULL DEFAULT '',
  `head_url` varchar(256) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),  UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
DROP TABLE IF EXISTS `news`;
CREATE TABLE `news` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `title` varchar(128) NOT NULL DEFAULT '',
  `link` varchar(256) NOT NULL DEFAULT '',
  `image` varchar(256) NOT NULL DEFAULT '',
  `like_count` int NOT NULL,
  `comment_count` int NOT NULL,
  `created_date` datetime NOT NULL,
  `user_id` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

//User.java 包含getter和setter，构造器，简单
//UserDAO.java
@Mapper
public interface UserDAO {
    String TABLE_NAME = "user";
    String INSET_FIELDS = "name, password, salt, head_url";
    String SELECT_FIELDS = "id, name, password, salt, head_url";
    //@Insert({"insert into user (name, password, slat, head_url) value ()"})
    @Insert({"insert into ", TABLE_NAME, "(", INSET_FIELDS,
            ") value (#{name}, #{password}, #{salt}, #{headUrl})"})
    int addUser(User user);
}
//InitDatabaseTests.java
@RunWith(SpringJUnit4ClassRunner.class)
@SpringApplicationConfiguration(classes = ToutiaoApplication.class)
@Sql("/init-schema.sql")
public class InitDatabaseTests {
   @Autowired
  UserDAO userDAO;
   @Test
   public void contextLoads() {
      Random random = new Random();
      for(int i = 0; i < 11; i++) { 
         User user = new User();
         user.setHeadUrl(String.format("http://images.nowcoder.com/head/%dt.png",random.nextInt(1000)));
         user.setName(String.format("USER%d", i));
         user.setPassword("");
         user.setSalt("");
         userDAO.addUser(user);
           }
         }
}