MyBatis的数据库操作入门(一) 旧城等待, 2021-09-27 10:36 309阅读 0赞 数据库代码 CREATE DATABASE /*!32312 IF NOT EXISTS*/`smbms` /*!40100 DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci */; USE `smbms`; CREATE TABLE `smbms_user` ( `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键ID', `userCode` varchar(15) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT '用户编码', `userName` varchar(15) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT '用户名称', `userPassword` varchar(15) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT '用户密码', `gender` int(10) DEFAULT NULL COMMENT '性别(1:女、 2:男)', `birthday` date DEFAULT NULL COMMENT '出生日期', `phone` varchar(15) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT '手机', `address` varchar(30) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT '地址', `userRole` int(10) DEFAULT NULL COMMENT '用户角色(取自角色表-角色id)', `createdBy` bigint(20) DEFAULT NULL COMMENT '创建者(userId)', `creationDate` datetime DEFAULT NULL COMMENT '创建时间', `modifyBy` bigint(20) DEFAULT NULL COMMENT '更新者(userId)', `modifyDate` datetime DEFAULT NULL COMMENT '更新时间', PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; insert into `smbms_user`(`id`,`userCode`,`userName`,`userPassword`,`gender`,`birthday`,`phone`,`address`,`userRole`,`createdBy`,`creationDate`,`modifyBy`,`modifyDate`) values (1,'admin','系统管理员','1234567',1,'1983-10-10','13688889999','北京市海淀区成府路207号',1,1,'2013-03-21 16:52:07',NULL,NULL),(2,'liming','李明','0000000',2,'1983-12-10','13688884457','北京市东城区前门东大街9号',2,1,'0000-00-00 00:00:00',NULL,NULL),(5,'hanlubiao','韩路彪','0000000',2,'1984-06-05','18567542321','北京市朝阳区北辰中心12号',2,1,'2014-12-31 19:52:09',NULL,NULL),(6,'zhanghua','张华','0000000',1,'1983-06-15','13544561111','北京市海淀区学院路61号',3,1,'2013-02-11 10:51:17',NULL,NULL),(7,'wangyang','王洋','0000000',2,'1982-12-31','13444561124','北京市海淀区西二旗辉煌国际16层',3,1,'2014-06-11 19:09:07',NULL,NULL),(8,'zhaoyan','赵燕','0000000',1,'1986-03-07','18098764545','北京市海淀区回龙观小区10号楼',3,1,'2016-04-21 13:54:07',NULL,NULL),(10,'sunlei','孙磊','0000000',2,'1981-01-04','13387676765','北京市朝阳区管庄新月小区12楼',3,1,'2015-05-06 10:52:07',NULL,NULL),(11,'sunxing','孙兴','0000000',2,'1978-03-12','13367890900','北京市朝阳区建国门南大街10号',3,1,'2016-11-09 16:51:17',NULL,NULL),(12,'zhangchen','张晨','0000000',1,'1986-03-28','18098765434','朝阳区管庄路口北柏林爱乐三期13号楼',3,1,'2016-08-09 05:52:37',1,'2016-04-14 14:15:36'),(13,'dengchao','邓超','0000000',2,'1981-11-04','13689674534','北京市海淀区北航家属院10号楼',3,1,'2016-07-11 08:02:47',NULL,NULL),(14,'yangguo','杨过','0000000',2,'1980-01-01','13388886623','北京市朝阳区北苑家园茉莉园20号楼',3,1,'2015-02-01 03:52:07',NULL,NULL),(15,'zhaomin','赵敏','0000000',1,'1987-12-04','18099897657','北京市昌平区天通苑3区12号楼',2,1,'2015-09-12 12:02:12',NULL,NULL); 数据库弄好了,顺便改下 UPDATE smbms\_user SET creationDate=NOW() WHERE ID=2; 使用Myeclipse创建一个Java project,Web project也行 ![70][] 然后新建一个文件夹lib ![70 1][] 导入MyBatis的jar包,放到lib文件夹 ![70 2][] (第一个是日志,第三个是源文件,第四个是数据库链接要用的) 然后选中这些jar包,右键 ![70 3][] 完成后会成为这样 ![70 4][] 编写实体类User.java 在src新建一个包,取包名字cn.bdqn.pojo ![70 5][] 新建一个java类,名字为User.java (toString方法的快捷键:alt+shift+s s,然后勾选你要生成的确定就行了) (get/set方法的快捷键:alt+shift+r ,然后选择你要生成的确定就行了) (无参数的构造函数:alt+shift+c) (带参数的构造函数:alt+shift+o,选择你要生成的就行了) 从上面几个我们可以看到,alt+shift后要点击什么呢?是看下划线决定的 ![70 6][] 回到正题,实体类代码如下: package cn.bdqn.pojo; import java.util.Date; public class User { /*CREATE TABLE `smbms_user` ( `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键ID', `userCode` varchar(15) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT '用户编码', `userName` varchar(15) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT '用户名称', `userPassword` varchar(15) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT '用户密码', `gender` int(10) DEFAULT NULL COMMENT '性别(1:女、 2:男)', `birthday` date DEFAULT NULL COMMENT '出生日期', `phone` varchar(15) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT '手机', `address` varchar(30) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT '地址', `userRole` int(10) DEFAULT NULL COMMENT '用户角色(取自角色表-角色id)', `createdBy` bigint(20) DEFAULT NULL COMMENT '创建者(userId)', `creationDate` datetime DEFAULT NULL COMMENT '创建时间', `modifyBy` bigint(20) DEFAULT NULL COMMENT '更新者(userId)', `modifyDate` datetime DEFAULT NULL COMMENT '更新时间', PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;*/ private Long id; private String userCode; private String userName; private String userPassword; private Integer gender; private String phone; private String address; private Date birthday; private Integer userRole; private Integer createdBy; private Date creationDate; private Integer modifyBy; private Date modifyDate; @Override public String toString() { return "User [address=" + address + ", birthday=" + birthday + ", createdBy=" + createdBy + ", creationDate=" + creationDate + ", gender=" + gender + ", id=" + id + ", modifyBy=" + modifyBy + ", modifyDate=" + modifyDate + ", phone=" + phone + ", userCode=" + userCode + ", userName=" + userName + ", userPassword=" + userPassword + ", userRole=" + userRole + "]"; } public User() { super(); // TODO Auto-generated constructor stub } public User(Long id, String userCode, String userName, String userPassword, Integer gender, String phone, String address, Date birthday, Integer userRole, Integer createdBy, Date creationDate, Integer modifyBy, Date modifyDate) { super(); this.id = id; this.userCode = userCode; this.userName = userName; this.userPassword = userPassword; this.gender = gender; this.phone = phone; this.address = address; this.birthday = birthday; this.userRole = userRole; this.createdBy = createdBy; this.creationDate = creationDate; this.modifyBy = modifyBy; this.modifyDate = modifyDate; } public Long getId() { return id; } public void setId(Long id) { this.id = id; } public String getUserCode() { return userCode; } public void setUserCode(String userCode) { this.userCode = userCode; } public String getUserName() { return userName; } public void setUserName(String userName) { this.userName = userName; } public String getUserPassword() { return userPassword; } public void setUserPassword(String userPassword) { this.userPassword = userPassword; } public Integer getGender() { return gender; } public void setGender(Integer gender) { this.gender = gender; } public String getPhone() { return phone; } public void setPhone(String phone) { this.phone = phone; } public String getAddress() { return address; } public void setAddress(String address) { this.address = address; } public Date getBirthday() { return birthday; } public void setBirthday(Date birthday) { this.birthday = birthday; } public Integer getUserRole() { return userRole; } public void setUserRole(Integer userRole) { this.userRole = userRole; } public Integer getCreatedBy() { return createdBy; } public void setCreatedBy(Integer createdBy) { this.createdBy = createdBy; } public Date getCreationDate() { return creationDate; } public void setCreationDate(Date creationDate) { this.creationDate = creationDate; } public Integer getModifyBy() { return modifyBy; } public void setModifyBy(Integer modifyBy) { this.modifyBy = modifyBy; } public Date getModifyDate() { return modifyDate; } public void setModifyDate(Date modifyDate) { this.modifyDate = modifyDate; } } 编写SQL映射文件UserMapper.xml 在src新建一个包,包名字为cn.bdqn.dao,新建一个file文件,名字为UserMapper.xml ![70 7][] 代码可以从帮助文档找(后面有说如何找)(入门-探究已映射的 SQL 语句) ![70 8][] 我们根据需要,改为 <?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="cn.bdqn.dao.UserMapper"> <select id="count" resultType="int"> select count(1) from smbms_user </select> </mapper> 命名空间namespace里面是sql的映射文件,可以右键那个xml文件,copy qualified name,复制进去,.xml和src前面都不要,还有/要变为. 这里可以写很多select文件,但是id要不一样,这里的id我设为count,id是唯一标识符 注意返回类型是什么,在这里返回是int类型(记录数) sql语句的意思是查询smbms\_user表的记录数,平时我们用的是\*,不过1的速度更快 接下来要做的:创建MyBatis配置文件mybatis-config.xml,配置数据库信息 在项目new一个Source Folder的文件夹,命名为resource,这个文件夹在程序编译的时候也会编译进去 ![70 9][] 然后在resource文件夹创建MyBatis配置文件mybatis-config.xml 方法一:点这里 ![70 10][] 然后在resource文件夹右键new的时候你可以看到 ![70 11][] 创建这个,名字为mybatis-config.xml 方法二:我按方法一还是没有看到 点击resource文件夹,右键,new,other ![70 12][] 输入xml,你也可以看到 ![70 13][] 直接点击,输入名字,确定就行 ![70 14][] 怎么写这个文件??? 百度MyBatis帮助文档 ![70 15][] 找到入门,往下拉 把这一段复制进去 ![70 16][] 然后在resource文件夹new两个File文件夹,名字分别是database.properties和log4j.properties ![70 17][] database.properties怎么写?? jdbc.driver=com.mysql.jdbc.Driver jdbc.url=jdbc:mysql://127.0.0.1:3306/smbms?useUnicode=true&characterEncoding=utf-8 jdbc.user=root jdbc.password=12345678 注意了:1:如果你是连接别人的mysql数据库,那么ip地址要和它的一致,可能就不是127.0.0.1了 2:你的密码要和你的数据库密码一致 3:因为你写了jdbc.,那么你在mybatis-config.xml也要改为相应的,当然你也可以删掉database.properties的jdbc. <?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> <!-- 引入属性文件 --> <properties resource="database.properties"></properties> <!-- 日志 --> <settings> <setting name="logImpl" value="LOG4J" /> </settings> <!-- 运行环境 --> <environments default="development"> <environment id="development"> <transactionManager type="JDBC" /> <dataSource type="POOLED"> <property name="driver" value="${jdbc.driver}" /> <property name="url" value="${jdbc.url}" /> <property name="username" value="${jdbc.user}" /> <property name="password" value="${jdbc.password}" /> </dataSource> </environment> </environments> <mappers> <mapper resource="cn/bdqn/dao/UserMapper.xml" /> </mappers> </configuration> 在这里我的value就改为了相应的了 在这里还要写入1:引入属性文件 2:日志(这个最好写,有错误也能看得舒服点,在帮助文档-日志里面就有代码)3:把映射文件(UserMapper.xml)路径写进去,修改完后,你可以全选-source-format,整理一下代码格式 log4j.properties怎么写??? log4j.rootLogger=DEBUG,CONSOLE,file #log4j.rootLogger=ERROR,ROLLING_FILE log4j.logger.cn.smbms.dao=debug log4j.logger.com.ibatis=debug log4j.logger.com.ibatis.common.jdbc.SimpleDataSource=debug log4j.logger.com.ibatis.common.jdbc.ScriptRunner=debug log4j.logger.com.ibatis.sqlmap.engine.impl.SqlMapClientDelegate=debug log4j.logger.java.sql.Connection=debug log4j.logger.java.sql.Statement=debug log4j.logger.java.sql.PreparedStatement=debug log4j.logger.java.sql.ResultSet=debug log4j.logger.org.tuckey.web.filters.urlrewrite.UrlRewriteFilter=debug ###################################################################################### # Console Appender \u65e5\u5fd7\u5728\u63a7\u5236\u8f93\u51fa\u914d\u7f6e ###################################################################################### log4j.appender.CONSOLE=org.apache.log4j.ConsoleAppender log4j.appender.Threshold=error log4j.appender.CONSOLE.Target=System.out log4j.appender.CONSOLE.layout=org.apache.log4j.PatternLayout log4j.appender.CONSOLE.layout.ConversionPattern= [%p] %d %c - %m%n ###################################################################################### # DailyRolling File \u6bcf\u5929\u4ea7\u751f\u4e00\u4e2a\u65e5\u5fd7\u6587\u4ef6\uff0c\u6587\u4ef6\u540d\u683c\u5f0f:log2009-09-11 ###################################################################################### log4j.appender.file=org.apache.log4j.DailyRollingFileAppender log4j.appender.file.DatePattern=yyyy-MM-dd log4j.appender.file.File=log.log log4j.appender.file.Append=true log4j.appender.file.Threshold=error log4j.appender.file.layout=org.apache.log4j.PatternLayout log4j.appender.file.layout.ConversionPattern=%d{yyyy-M-d HH:mm:ss}%x[%5p](%F:%L) %m%n log4j.logger.com.opensymphony.xwork2=error 最后:编写测试类Test.java,运行输出结果 在src新建一个包cn.bdqn.test,创建一个java类,名字为Test 参考帮助文档(一般都在入门那里) ![70 18][] 改写后 package cn.bdqn.test; import java.io.IOException; import java.io.InputStream; import org.apache.ibatis.io.Resources; import org.apache.ibatis.session.SqlSession; import org.apache.ibatis.session.SqlSessionFactory; import org.apache.ibatis.session.SqlSessionFactoryBuilder; import org.apache.log4j.Logger; public class Test { private static Logger logger=Logger.getLogger("Test"); public static void main(String[] args) { SqlSession session = null; try { //读取核心配置文件mybatis-config.xml String resource = "mybatis-config.xml"; InputStream inputStream = Resources.getResourceAsStream(resource); //创建SqlSessionFactory对象,读取配置文件 SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream); //创建SqlSession对象 session = sqlSessionFactory.openSession(); //调用mapper文件进行数据操作 int count=session.selectOne("cn.bdqn.dao.UserMapper.count"); logger.debug("用户数量是:"+count); } catch (IOException e) { // TODO Auto-generated catch block e.printStackTrace(); }finally{ if(session!=null){ session.close(); } } } } 运行后 \[DEBUG\] 2018-09-29 11:30:48,335 org.apache.ibatis.logging.LogFactory - Logging initialized using 'class org.apache.ibatis.logging.log4j.Log4jImpl' adapter. /\*使用“类Org.apache.ibatis.log.4j.log4jimpl”适配器初始化的日志记录。\*/ \[DEBUG\] 2018-09-29 11:30:48,335 org.apache.ibatis.logging.LogFactory - Logging initialized using 'class org.apache.ibatis.logging.log4j.Log4jImpl' adapter. \[DEBUG\] 2018-09-29 11:30:48,351 org.apache.ibatis.datasource.pooled.PooledDataSource - PooledDataSource forcefully closed/removed all connections. /\*数据源有力地关闭/删除了所有连接。\*/ \[DEBUG\] 2018-09-29 11:30:48,351 org.apache.ibatis.datasource.pooled.PooledDataSource - PooledDataSource forcefully closed/removed all connections. \[DEBUG\] 2018-09-29 11:30:48,351 org.apache.ibatis.datasource.pooled.PooledDataSource - PooledDataSource forcefully closed/removed all connections. \[DEBUG\] 2018-09-29 11:30:48,351 org.apache.ibatis.datasource.pooled.PooledDataSource - PooledDataSource forcefully closed/removed all connections. \[DEBUG\] 2018-09-29 11:30:48,429 org.apache.ibatis.transaction.jdbc.JdbcTransaction - Opening JDBC Connection /\*打开jdbc连接\*/ \[DEBUG\] 2018-09-29 11:30:48,619 org.apache.ibatis.datasource.pooled.PooledDataSource - Created connection 972765878. /\*创建连接972765878。\*/ \[DEBUG\] 2018-09-29 11:30:48,619 cn.bdqn.dao.UserMapper.count - ooo Using Connection \[com.mysql.jdbc.JDBC4Connection@39fb3ab6\] \[DEBUG\] 2018-09-29 11:30:48,619 cn.bdqn.dao.UserMapper.count - ==> Preparing: select count(1) from smbms\_user (准备,sql语句) \[DEBUG\] 2018-09-29 11:30:48,650 cn.bdqn.dao.UserMapper.count - ==> Parameters: (参数) \[DEBUG\] 2018-09-29 11:30:48,665 Test - 用户数量是:12(结果) \[DEBUG\] 2018-09-29 11:30:48,665 org.apache.ibatis.transaction.jdbc.JdbcTransaction - Resetting autocommit to true on JDBC Connection \[com.mysql.jdbc.JDBC4Connection@39fb3ab6\] \[DEBUG\] 2018-09-29 11:30:48,665 org.apache.ibatis.transaction.jdbc.JdbcTransaction - Closing JDBC Connection \[com.mysql.jdbc.JDBC4Connection@39fb3ab6\] /\*关闭数据库链接\*/ \[DEBUG\] 2018-09-29 11:30:48,665 org.apache.ibatis.datasource.pooled.PooledDataSource - Returned connection 972765878 to pool. [70]: /images/20210923/e72f26d268424bd3b1bfca340ec80f9b.png [70 1]: /images/20210923/b120297957eb421fa5da107860a6390d.png [70 2]: /images/20210923/7c456ac3e5584195a0b55a45f21569f6.png [70 3]: /images/20210923/06034d2264f244e4ba19656ce392d14e.png [70 4]: /images/20210923/a55e6fc31f8646cd98b3f21581b61c9b.png [70 5]: /images/20210923/28c32610082f411f8b3544f2ee613c75.png [70 6]: /images/20210923/b365265a639a484dae591405077dcef3.png [70 7]: /images/20210923/d9e409349e2c4e0f8cbf0926ce058435.png [70 8]: /images/20210923/83c44facacef49ac9898365cea70e091.png [70 9]: /images/20210923/4236baad7bc14899aa37808ae534b54e.png [70 10]: /images/20210923/547242b69081494eb642412db985919c.png [70 11]: /images/20210923/2bd96b35b93c46c796e3bcbe5df082d8.png [70 12]: /images/20210923/0ae500ab86974d028343873958031c80.png [70 13]: /images/20210923/c90dd05b44614d22b4aa44dae89d4a84.png [70 14]: /images/20210923/5c75029a2df14893974abd827d4ea540.png [70 15]: /images/20210923/b097ef5fdaaf4936af7877d5f36106ce.png [70 16]: /images/20210923/1abebb5b84e34d2082b018495ba8a8f4.png [70 17]: /images/20210923/420b201fb7d54d699302de529b85a6f9.png [70 18]: /images/20210923/df4ac2c331a0499ea16d8ee93bed4d52.png
还没有评论,来说两句吧...