mybatis连接mysql数据库实现的jdbc功能
最近公司项目要使用myBatis，自己以前没有接触过，就在网上找到了一些资料研究了些。初步做出了基于myBatis连接mysql数据库的jdbc实现的功能。项目需要的jar包下载地址：http://pan.baidu.com/s/1AWnPcemployee.javapackage com.org.position.model;
public class employee {
private int employeeId;// 员工id
private StringemployeeName; //员工姓名
private StringemployeeSax; //员工性别
private StringemployeePost; //员工职务
private StringemployeeDepartment; //员工所在部门
public int getEmployeeId() {
return employeeId;
}
public void setEmployeeId(int employeeId) {
this.employeeId = employeeId;
}
public String getEmployeeName() {
return employeeName;
}
public void setEmployeeName(String employeeName) {
this.employeeName = employeeName;
}
public String getEmployeeSax() {
return employeeSax;
}
public void setEmployeeSax(String employeeSax) {
this.employeeSax = employeeSax;
}
public String getEmployeePost() {
return employeePost;
}
public void setEmployeePost(String employeePost) {
this.employeePost = employeePost;
}
public String getEmployeeDepartment() {
return employeeDepartment;
}
public void setEmployeeDepartment(String employeeDepartment) {
this.employeeDepartment = employeeDepartment;
}
}employeeDao.javapackage com.org.position.dao;
import java.util.List;
import com.org.position.model.employee;
public interface employeeDao {
public int countAll();
public employee findEnterprise(int employeeId);
public int insertEnterprise(employee emp);
public int deleteEnterprise(int employeeId);
public List<employee> getAllEnterprise();
public int updateEnterprise(employee emp );
public List<employee> findEnterpriseByExampleIf(employee emp);
}employeeDaoimpal.javapackage com.org.position.dao.impal;
import java.io.IOException;
import java.io.Reader;
import java.util.List;
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 com.org.position.dao.employeeDao;
import com.org.position.model.employee;
public class employeeDaoimpal implements employeeDao {
@Override
public int countAll() {
String resource="mybaits-config.xml";
Reader reader=null;
SqlSessionFactoryssf=null;
SqlSession session=null;
int iCount=0;
try {
reader= Resources.getResourceAsReader(resource);
SqlSessionFactoryBuilder builder =new SqlSessionFactoryBuilder();
ssf=builder.build(reader);
session=ssf.openSession();
iCount=session.selectOne("com.org.position.dao.employeeDao.countAll");
} catch (IOException e) {
e.printStackTrace();
}
finally{
session.close();
}
return iCount;
}
@Override
public employee findEnterprise(int employeeId) {
String resource="mybaits-config.xml";
Reader reader=null;
SqlSessionFactoryssf=null;
SqlSession session=null;
employeeemp=null;
try {
reader = Resources.getResourceAsReader(resource);
SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder();
ssf = builder.build(reader);
session=ssf.openSession();
employeeDao empdao=session.getMapper(employeeDao.class);
emp=empdao.findEnterprise(employeeId);
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
session.close();
}
return emp;
}
@Override
public int insertEnterprise(employee emp) {
String resource="mybaits-config.xml";
Reader reader=null;
SqlSessionFactoryssf=null;
SqlSession session=null;
int count=0;
try {
reader = Resources.getResourceAsReader(resource);
SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder();
ssf = builder.build(reader);
session=ssf.openSession();
count=session.insert("com.org.position.dao.employeeDao.insertEnterprise",emp);
session.commit();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
finally{
session.close();
}
return count;
}
@Override
public int deleteEnterprise(int employeeId) {
String resource="mybaits-config.xml";
Reader reader = null;
SqlSessionFactory ssf=null;
SqlSession session=null;
int iCount = 0;
try {
reader = Resources.getResourceAsReader(resource);
SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder();
ssf = builder.build(reader);
session=ssf.openSession();
iCount=session.delete("com.org.position.dao.employeeDao.deleteEnterprise", employeeId);
session.commit();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
session.close();
}
return iCount;
}
@Override
public List<employee> getAllEnterprise() {
String resource="mybaits-config.xml";
Reader reader = null;
SqlSessionFactory ssf=null;
SqlSession session=null;
List<employee> emps = null;
try {
reader = Resources.getResourceAsReader(resource);
SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder();
ssf = builder.build(reader);
session=ssf.openSession();
emps=session.selectList("com.org.position.dao.employeeDao.getAllEnterprise");
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
finally{
session.close();
}
return emps;
}
@Override
public int updateEnterprise(employee emp) {
String resource="mybaits-config.xml";
Reader reader = null;
SqlSessionFactory ssf=null;
SqlSession session=null;
int iCount = 0;
try {
reader = Resources.getResourceAsReader(resource);
SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder();
ssf = builder.build(reader);
session=ssf.openSession();
iCount=session.delete("com.org.position.dao.employeeDao.updateEnterprise",emp);
session.commit();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
session.close();
}
return iCount;
}
@Override
public List<employee> findEnterpriseByExampleIf(employee emp) {
String resource="mybatis-config.xml";
Reader reader = null;
SqlSessionFactory ssf=null;
SqlSession session=null;
List<employee> emps = null;
try {
reader = Resources.getResourceAsReader(resource);
SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder();
ssf = builder.build(reader);
session=ssf.openSession();
emps=session.selectList("com.org.position.dao.employeeDao.findEnterpriseByExampleIf",emp);
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
session.close();
}
return emps;
}
}employeeDaoMapper.xml<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//ibatis.apache.org//DTD Mapper 3.0//EN"
"http://ibatis.apache.org/dtd/ibatis-3-mapper.dtd">
<mappernamespace="com.org.position.dao.employeeDao">
<resultMap type="com.org.position.model.employee" id="empResultMap">
<id property="employeeId" column="employeeId"/>
<result property="employeeName" column="employeeName" />
<result property="employeeSax" column="employeeSax"/>
<result property="employeePost" column="employeePost"/>
<result property="employeeDepartment" column="employeeDepartment"/>
</resultMap>
<select id="findEnterpriseByExampleIf" parameterType="com.org.position.model.employee"resultMap="empResultMap">
select * from employee where 1=1
<if test="employeed !=null">
and employeeId=#{employeeId}
</if>
<if test="employeename!=null">
and employeeName=#{employeeName}
</if>
<if test="employeesax! =null">
and employeeSax=#{employeeSax}
</if>
<if test="employeepost!=null">
and employeePost=#{employeePost}
</if>
<if test="employeedepartment! =null">
andemployeeDepartment=#{employeeDepartment}
</if>
</select>
<select id="countAll"resultType="int">
select count(*) from t_employee
</select>
<!-- 根据id查询对应的数据信息 -->
<select id="findEnterprise" parameterType="int" resultType="com.org.position.model.employee">
select * from t_employee where employeeId=#{employeeId}
</select>
<!-- 查询所有 数据 -->
<select id="getAllEnterprise" resultType="com.org.position.model.employee">
select * from t_employee
</select>
<!-- 添加数据信息 -->
<insert id="insertEnterprise" parameterType="com.org.position.model.employee">
insert intot_employee(employeeId,employeeName,employeeSax,employeePost,employeeDepartment)
values (#{employeeId},#{employeeName},#{employeeSax},#{employeePost},#{employeeDepartment})
</insert>
<!-- 更新数据信息 -->
<update id="updateEnterprise" parameterType="com.org.position.model.employee">
update t_employee set employeeName=#{employeeName},employeeSax=#{employeeSax},employeePost=#{employeePost},employeeDepartment=#{employeeDepartment}
where employeeId=#{employeeId}
</update>
<!-- 删除数据信息 -->
<delete id="deleteEnterprise" parameterType="com.org.position.model.employee">
delete from t_employee where employeeId=#{employeeId}
</delete>
</mapper>mybaits-config.xm<?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"></transactionManager>
<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="mysql"/>
</dataSource>
</environment>
</environments>
<mappers>
<mapper resource="com/org/position/dao/employeeDaoMapper.xml"/>
</mappers>
</configuration>mybaits-config.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"></transactionManager>
<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="mysql"/>
</dataSource>
</environment>
</environments>
<mappers>
<mapper resource="com/org/position/dao/employeeDaoMapper.xml"/>
</mappers>
</configuration>employeeTest.javapackage com.org.position.test;
import java.util.List;
import com.org.position.dao.employeeDao;
import com.org.position.dao.impal.employeeDaoimpal;
import com.org.position.model.employee;
public class employeeTest {
public static void testAddEmp(){
employee emp=new employee();
employeeDao dao=new employeeDaoimpal();
emp.setEmployeeId(11);
emp.setEmployeeName("爽");
emp.setEmployeeSax("女");
emp.setEmployeePost("测试经理");
emp.setEmployeeDepartment("研发部");
int count =dao.insertEnterprise(emp);
if (count==1) {
System.out.println("员工信息录入成功！");
}else {
System.out.println("员工信息录入失败！");
}
}
public static void testUpdateEmp(){
employee emp=new employee();
emp.setEmployeeId(1);
emp.setEmployeeName("雷浩明");
emp.setEmployeeSax("男");
emp.setEmployeePost("CEO");
emp.setEmployeeDepartment("总裁办");
employeeDao dao=new employeeDaoimpal();
int count =dao.updateEnterprise(emp);
if (count==1) {
System.out.println("员工信息修改成功！");
}else {
System.out.println("员工信息修改失败！");
}
}
public static void testSelectEmp(){
}
public static void testDeleteEmp(){
employeeDao dao =new employeeDaoimpal();
int count = dao.deleteEnterprise(11);
if(count == 1){
System.out.println("员工信息删除成功");
}else {
System.out.println("员工信息删除失败");
}
}
public static void testGetCount(){
employeeDao dao =new employeeDaoimpal();
System.out.println("共有"+dao.countAll()+"名员工");
}
public static void testGetEmpById(){
employeeDao dao =new employeeDaoimpal();
employee emp=dao.findEnterprise(1);
System.out.println("职务是:"+emp.getEmployeePost()+"所在部门:"+emp.getEmployeeDepartment());
}
public static void testGetAll(){
employeeDao dao =new employeeDaoimpal();
List<employee> emps = dao.getAllEnterprise();
System.out.println("西安涯创信息技术有限公司员工明细");
System.out.println("---------------------------");
System.out.println("员工编号\t员工姓名\t员工职位\t所在部门");
for (employee emp : emps) {
System.out.println(emp.getEmployeeId()+"\t"+emp.getEmployeeName()+"\t"+emp.getEmployeePost()+"\t"+emp.getEmployeeDepartment());
}
}
public static void main(String[] args) {
//testAddEmp();
//testUpdateEmp();
//testDeleteEmp();
//testGetCount();
//testGetEmpById();
testGetAll();
}
}t_employee.sql/*
SQLyog Ultimate v8.32
MySQL - 5.0.27-community : Database - test
*********************************************************************
*/
/*!40101 SET NAMES utf8 */;
/*!40101 SET SQL_MODE=''*/;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
CREATE DATABASE /*!32312 IF NOT EXISTS*/`test` /*!40100 DEFAULT CHARACTER SET utf8 */;
USE `test`;
/*Table structure for table `t_employee` */
DROP TABLE IF EXISTS `t_employee`;
CREATE TABLE `t_employee` (
`employeeid` int(10) NOT NULL COMMENT '员工id',
`employeename` varchar(20) NOT NULL COMMENT '员工姓名',
`employeesax` varchar(20) default NULL COMMENT '员工性别',
`employeepost` varchar(30) NOT NULL COMMENT '员工职务',
`employeedepartment` varchar(30) NOT NULL default '' COMMENT '员工所在部门',
PRIMARY KEY(`employeeid`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
/*Data for the table `t_employee` */
insertinto `t_employee`(`employeeid`,`employeename`,`employeesax`,`employeepost`,`employeedepartment`) values (1,'雷浩明','男','CEO','总裁办'),(2,'刘军','男','执行总裁','总裁办'),(3,'李海涛','男','总经理','总经办'),(4,'边肖','男','CTO','技术部'),(5,'柳青','女','经理','经营管理部'),(6,'高晓梅','女','财务','财务部'),(7,'曹舜水','男','研发经理','研发部'),(8,'董华','男','项目经理','研发部'),(9,'张军华','男','产品经理','产品部'),(10,'曾爽','女','测试经理','研发部');
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
页:
[1]