MySQL安装和使用
案例1:MySQL使用练习
-
创建一个数据库,库名ovls
create database ovls default charset utf8;
-
在ovls库中创建一个video表
id INT PK 主键 name VARCHAR (50) 视频名称 url VARCHAR (100) not Null 视频路径 chapter_id INT 章节id
use ovls;
create table video(
id int primary key autoincrement,
name varchar(50),
url varchar(100),
chapterid int
) engine=innodb default charset=utf8; -
向video表插入以下记录
1 Java01 /java/01/1.avi 1 2 Java02 /java/01/2.avi 1 3 Java03 /java/01/3.avi 2
insert into video (name,url,chapter_id)values ('Java01','/java/01/1.avi',1);
-
将video表某条记录删除
delete from video where id=5;
-
将video表某条记录name值更新
update video set name='Java03' where id=2;
-
查询video表第2页记录,每页5条
select * from video limit 5,5;
案例2:构建项目数据库结构
需求:新建一个studyonline库,导入studyonline.sql数据库结构。
-
创建studyonline库
create database studyonline default charset=utf8;
-
使用studyonline库
use studyonline;
-
导入studyonline.sql文件(UTF-8编码文件)
set names utf8; source xxx/xxx/studyonline.sql;
案例3:利用MyBatis-Generator工具
可以根据现有数据库生成实体类和Mapper接口。
-
在pom.xml中导入mybatis-generator工具包
<dependencies> <dependency> <groupId>org.mybatis.generator</groupId> <artifactId>mybatis-generator-core</artifactId> <version>1.3.6</version> </dependency> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>5.1.8</version> </dependency> </dependencies>
-
在project下创建一个mbg.xml配置文件,修改定义
<?xml version="1.0" encoding="UTF-8"?> <!DOCTYPE generatorConfiguration PUBLIC "-//mybatis.org//DTD MyBatis Generator Configuration 1.0//EN" "http://mybatis.org/dtd/mybatis-generator-config_1_0.dtd"> <generatorConfiguration> <!-- 指定驱动包 --> <classPathEntry location="C:\Users\Administrator\.m2\repository\mysql\mysql-connector-Java\5.1.8\mysql-connector-java-5.1.8.jar" /> <!-- 指定连接参数 --> <context id="MySQLTables" targetRuntime="MyBatis3"> <jdbcConnection driverClass="com.mysql.jdbc.Driver" connectionURL="jdbc:mysql://localhost:3306/studyonline" userId="root" password="1234"> </jdbcConnection> <!-- 指定Java实体类存放包路径 --> <javaModelGenerator targetPackage="cn.xdl.ovls.entity" targetProject=".\src\main\java"> <property name="enableSubPackages" value="true" /> <property name="trimStrings" value="true" /> </javaModelGenerator> <!-- 指定SQL定义XML文件存放位置 --> <sqlMapGenerator targetPackage="cn.xdl.ovls.sql" targetProject=".\src\main\java"> <property name="enableSubPackages" value="true" /> </sqlMapGenerator> <!-- 指定Mapper映射器接口存放位置Dao --> <!-- type指定为XMLMAPPER意思是生成SQL的XML文件; type指定为ANNOTATEDMAPPER意思是将SQL生成到Mapper接口中,sqlMapGenerator无效 --> <javaClientGenerator type="ANNOTATEDMAPPER" targetPackage="cn.xdl.ovls.dao" targetProject=".\src\main\java"> <property name="enableSubPackages" value="true" /> </javaClientGenerator> <!-- 指定将哪些表生成 --> <table tableName="%" enableCountByExample="false" enableSelectByExample="false" enableDeleteByExample="false" enableUpdateByExample="false"> </table> </context> </generatorConfiguration>
-
定义一个启动类,加载mbg.xml执行生成工具
public class MyBatisRunner { public static void main(String[] args) throws Exception{ List<String> warnings = new ArrayList<String>(); boolean overwrite = true; File configFile = new File("mbg.xml"); ConfigurationParser cp = new ConfigurationParser(warnings); Configuration config = cp.parseConfiguration(configFile); DefaultShellCallback callback = new DefaultShellCallback(overwrite); MyBatisGenerator myBatisGenerator = new MyBatisGenerator(config, callback, warnings); myBatisGenerator.generate(null); } }