package com.mybatis3.mappers;

 import java.util.List;

 import com.mybatis3.domain.Student;

 /**

  * @author Siva

  *

  */

 public interface StudentMapper

 {

     List<Student> findAllStudents();

     Student findStudentById(Integer id);

     void insertStudent(Student student);

     void updateStudent(Student student);

 }

 <?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.mybatis3.mappers.StudentMapper">

     <resultMap type="Student" id="StudentResult">

         <id     property="studId" column="stud_id"/>

         <result property="name" column="name"/>

         <result property="email" column="email"/>

         <result property="dob" column="dob"/>

     </resultMap>

       <select id="findAllStudents" resultMap="StudentResult">

         select * from Students

       </select>

       <select id="findStudentById" parameterType="int" resultType="Student">

         select stud_id as studId, name, email, dob from Students where stud_id=#{studId}

       </select>

       <insert id="insertStudent" parameterType="Student">

           INSERT INTO STUDENTS(STUD_ID,NAME,EMAIL,DOB) VALUES(#{studId},#{name},#{email},#{dob})

       </insert>

       <update id="updateStudent" parameterType="Student">

           UPDATE STUDENTS SET NAME=#{name}, EMAIL=#{email}, DOB=#{dob} WHERE STUD_ID=#{studId}

       </update>

 </mapper>

 package com.mybatis3.services;

 import java.util.List;

 import org.apache.ibatis.session.SqlSession;

 import org.slf4j.Logger;

 import org.slf4j.LoggerFactory;

 import com.mybatis3.domain.Student;

 import com.mybatis3.mappers.StudentMapper;

 import com.mybatis3.util.MyBatisSqlSessionFactory;

 /**

  * @author Siva

  *

  */

 public class StudentService

 {

     private Logger logger = LoggerFactory.getLogger(getClass());

     public List<Student> findAllStudents()

     {

         SqlSession sqlSession = MyBatisSqlSessionFactory.getSqlSession();

         try {

             StudentMapper studentMapper = sqlSession.getMapper(StudentMapper.class);

             return studentMapper.findAllStudents();

         } finally {

             sqlSession.close();

         }

     }

     public Student findStudentById(Integer studId)

     {

         logger.debug("Select Student By ID :{}", studId);

         SqlSession sqlSession = MyBatisSqlSessionFactory.getSqlSession();

         try {

             StudentMapper studentMapper = sqlSession.getMapper(StudentMapper.class);

             return studentMapper.findStudentById(studId);

             //return sqlSession.selectOne("com.mybatis3.StudentMapper.findStudentById", studId);

         } finally {

             sqlSession.close();

         }

     }

     public void createStudent(Student student)

     {

         SqlSession sqlSession = MyBatisSqlSessionFactory.getSqlSession();

         try {

             StudentMapper studentMapper = sqlSession.getMapper(StudentMapper.class);

             studentMapper.insertStudent(student);

             sqlSession.commit();

         } finally {

             sqlSession.close();

         }

     }

     public void updateStudent(Student student)

     {

         SqlSession sqlSession = MyBatisSqlSessionFactory.getSqlSession();

         try {

             StudentMapper studentMapper = sqlSession.getMapper(StudentMapper.class);

             studentMapper.updateStudent(student);

             sqlSession.commit();

         } finally {

             sqlSession.close();

         }

     }

 }

 package com.mybatis3.services;

 import java.sql.Connection;

 import java.sql.DriverManager;

 import java.sql.PreparedStatement;

 import java.sql.ResultSet;

 import java.sql.SQLException;

 import java.util.Date;

 import com.mybatis3.domain.Student;

 /**

  * @author Siva

  *

  */

 public class JdbcStudentService

 {

     private static final String DRIVER = "com.mysql.jdbc.Driver";

     private static final String URL = "jdbc:mysql://localhost:3306/elearning";

     private static final String USERNAME = "root";

     private static final String PASSWORD = "admin";

     public static void main(String[] args)

     {

         JdbcStudentService service = new JdbcStudentService();

         Student existingStudent = service.findStudentById(1);

         System.out.println(existingStudent);

         long ts = System.currentTimeMillis();//For creating unique student names

         Student newStudent = new Student(0,"student_"+ts,"student_"+ts+"@gmail.com",new Date());

         service.createStudent(newStudent);

         System.out.println(newStudent);

         int updateStudId = 3;

         Student updateStudent = service.findStudentById(updateStudId);

         ts = System.currentTimeMillis();//For creating unique student email

         updateStudent.setEmail("student_"+ts+"@gmail.com");

         service.updateStudent(updateStudent);

     }

     public Student findStudentById(int studId)

     {

         Student student = null;

         Connection conn = null;

         try

         {

             conn = getDatabaseConnection();

             String sql = "select * from students where stud_id=?";

             PreparedStatement pstmt = conn.prepareStatement(sql);

             pstmt.setInt(1, studId);

             ResultSet rs = pstmt.executeQuery();

             if(rs.next())

             {

                 student = new Student();

                 student.setStudId(rs.getInt("stud_id"));

                 student.setName(rs.getString("name"));

                 student.setEmail(rs.getString("email"));

                 student.setDob(rs.getDate("dob"));

             }

         } catch (SQLException e)

         {

             throw new RuntimeException(e);

         }

         finally

         {

             if(conn!= null){

                 try {

                     conn.close();

                 } catch (SQLException e){ }

             }

         }

         return student;

     }

     public void createStudent(Student student)

     {

         Connection conn = null;

         try

         {

             conn = getDatabaseConnection();

             String sql = "INSERT INTO STUDENTS(STUD_ID,NAME,EMAIL,DOB) VALUES(?,?,?,?)";

             PreparedStatement pstmt = conn.prepareStatement(sql);

             pstmt.setInt(1, student.getStudId());

             pstmt.setString(2, student.getName());

             pstmt.setString(3, student.getEmail());

             pstmt.setDate(4, new java.sql.Date(student.getDob().getTime()));

             pstmt.executeUpdate();

         } catch (SQLException e)

         {

             throw new RuntimeException(e);

         }

         finally

         {

             if(conn!= null){

                 try {

                     conn.close();

                 } catch (SQLException e){ }

             }

         }

     }

     public void updateStudent(Student student)

     {

         Connection conn = null;

         try

         {

             conn = getDatabaseConnection();

             conn = getDatabaseConnection();

             String sql = "UPDATE STUDENTS SET NAME=?,EMAIL=?,DOB=? WHERE STUD_ID=?";

             PreparedStatement pstmt = conn.prepareStatement(sql);

             pstmt.setString(1, student.getName());

             pstmt.setString(2, student.getEmail());

             pstmt.setDate(3, new java.sql.Date(student.getDob().getTime()));

             pstmt.setInt(4, student.getStudId());

             pstmt.executeUpdate();

         } catch (SQLException e)

         {

             throw new RuntimeException(e.getCause());

         }

         finally

         {

             if(conn!= null){

                 try {

                     conn.close();

                 } catch (SQLException e){ }

             }

         }

     }

     protected Connection getDatabaseConnection() throws SQLException

     {

         try

         {

             Class.forName(JdbcStudentService.DRIVER);

             return DriverManager.getConnection(JdbcStudentService.URL,

                                                 JdbcStudentService.USERNAME,

                                                 JdbcStudentService.PASSWORD);

         } catch (SQLException e)

         {

             throw e;

         } catch (Exception e)

         {

             throw new RuntimeException(e.getCause());

         }

     }

 }

 package com.mybatis3.domain;

 import java.util.Date;

 /**

  * @author Siva

  *

  */

 public class Student

 {

     private Integer studId;

     private String name;

     private String email;

     private Date dob;

     public Student() {

     }

     public Student(Integer studId) {

         this.studId = studId;

     }

     public Student(Integer studId, String name, String email, Date dob) {

         this.studId = studId;

         this.name = name;

         this.email = email;

         this.dob = dob;

     }

     @Override

     public String toString() {

         return "Student [studId=" + studId + ", name=" + name + ", email="

                 + email + ", dob=" + dob + "]";

     }

     public Integer getStudId() {

         return studId;

     }

     public void setStudId(Integer studId) {

         this.studId = studId;

     }

     public String getName() {

         return name;

     }

     public void setName(String name) {

         this.name = name;

     }

     public String getEmail() {

         return email;

     }

     public void setEmail(String email) {

         this.email = email;

     }

     public Date getDob() {

         return dob;

     }

     public void setDob(Date dob) {

         this.dob = dob;

     }

 }

 package com.mybatis3.util;

 import java.io.IOException;

 import java.io.InputStream;

 import java.sql.Connection;

 import java.sql.DriverManager;

 import java.util.Properties;

 import org.apache.ibatis.datasource.DataSourceFactory;

 import org.apache.ibatis.io.Resources;

 import org.apache.ibatis.session.SqlSession;

 import org.apache.ibatis.session.SqlSessionFactory;

 import org.apache.ibatis.session.SqlSessionFactoryBuilder;

 /**

  * @author Siva

  *

  */

 public class MyBatisSqlSessionFactory

 {

     private static SqlSessionFactory sqlSessionFactory;

     private static final Properties PROPERTIES = new Properties();

     static

     {

         try {

             InputStream is = DataSourceFactory.class.getResourceAsStream("/application.properties");

             PROPERTIES.load(is);

         } catch (IOException e) {

             e.printStackTrace();

         }

     }

     public static SqlSessionFactory getSqlSessionFactory()

     {

         if(sqlSessionFactory==null)

         {

             InputStream inputStream = null;

             try

             {

                 inputStream = Resources.getResourceAsStream("mybatis-config.xml");

                 sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);

             }catch (IOException e)

             {

                 throw new RuntimeException(e.getCause());

             }finally {

                 if(inputStream != null){

                     try {

                         inputStream.close();

                     } catch (IOException e) {

                     }

                 }

             }

         }

         return sqlSessionFactory;

     }

     public static SqlSession getSqlSession()

     {

         return getSqlSessionFactory().openSession();

     }

     public static Connection getConnection()

     {

         String driver = PROPERTIES.getProperty("jdbc.driverClassName");

         String url = PROPERTIES.getProperty("jdbc.url");

         String username = PROPERTIES.getProperty("jdbc.username");

         String password = PROPERTIES.getProperty("jdbc.password");

         Connection connection = null;

         try {

             Class.forName(driver);

             connection = DriverManager.getConnection(url, username, password);

         } catch (Exception e) {

             throw new RuntimeException(e);

         }

         return connection;

     }

 }

 <?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="application.properties"/>

       <typeAliases>

           <package name="com.mybatis3.domain"/>

       </typeAliases>

       <environments default="development">

         <environment id="development">

           <transactionManager type="JDBC"/>

           <dataSource type="POOLED">

             <property name="driver" value="${jdbc.driverClassName}"/>

             <property name="url" value="${jdbc.url}"/>

             <property name="username" value="${jdbc.username}"/>

             <property name="password" value="${jdbc.password}"/>

           </dataSource>

         </environment>

       </environments>

       <mappers>

         <mapper resource="com/mybatis3/mappers/StudentMapper.xml"/>

       </mappers>

 </configuration>

 ################### DataSource Configuration ##########################

 jdbc.driverClassName=com.mysql.jdbc.Driver

 jdbc.url=jdbc:mysql://localhost:3306/elearning

 jdbc.username=root

 jdbc.password=1234

 CREATE TABLE ADDRESSES

 (

   ADDR_ID INT(11) NOT NULL AUTO_INCREMENT,

   STREET VARCHAR(50) NOT NULL,

   CITY VARCHAR(50) NOT NULL,

   STATE VARCHAR(50) NOT NULL,

   ZIP VARCHAR(10) DEFAULT NULL,

   COUNTRY VARCHAR(50) NOT NULL,

   PRIMARY KEY (ADDR_ID)

 ) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=LATIN1;

 CREATE TABLE STUDENTS

 (

   STUD_ID INT(11) NOT NULL AUTO_INCREMENT,

   NAME VARCHAR(50) NOT NULL,

   EMAIL VARCHAR(50) NOT NULL,

   PHONE VARCHAR(15) DEFAULT NULL,

   DOB DATE DEFAULT NULL,

   BIO LONGTEXT DEFAULT NULL,

   PIC BLOB DEFAULT NULL,

   ADDR_ID INT(11) DEFAULT NULL,

   PRIMARY KEY (STUD_ID),

   CONSTRAINT FK_STUDENTS_ADDR FOREIGN KEY (ADDR_ID) REFERENCES ADDRESSES (ADDR_ID)

 ) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=LATIN1;

 CREATE TABLE TUTORS

 (

   TUTOR_ID INT(11) NOT NULL AUTO_INCREMENT,

   NAME VARCHAR(50) NOT NULL,

   EMAIL VARCHAR(50) NOT NULL,

   PHONE VARCHAR(15) DEFAULT NULL,

   DOB DATE DEFAULT NULL,

   BIO LONGTEXT DEFAULT NULL,

   PIC BLOB DEFAULT NULL,

   ADDR_ID INT(11) DEFAULT NULL,

   PRIMARY KEY (TUTOR_ID),

   CONSTRAINT FK_TUTORS_ADDR FOREIGN KEY (ADDR_ID) REFERENCES ADDRESSES (ADDR_ID)

 ) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=LATIN1;

 CREATE TABLE COURSES

 (

   COURSE_ID INT(11) NOT NULL AUTO_INCREMENT,

   NAME VARCHAR(100) NOT NULL,

   DESCRIPTION VARCHAR(512) DEFAULT NULL,

   START_DATE DATE DEFAULT NULL,

   END_DATE DATE DEFAULT NULL,

   TUTOR_ID INT(11) NOT NULL,

   PRIMARY KEY (COURSE_ID),

   CONSTRAINT FK_COURSE_TUTOR FOREIGN KEY (TUTOR_ID) REFERENCES TUTORS (TUTOR_ID)

 ) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=LATIN1;

 CREATE TABLE COURSE_ENROLLMENT

 (

   COURSE_ID INT(11) NOT NULL,

   STUD_ID INT(11) NOT NULL,

   PRIMARY KEY (COURSE_ID,STUD_ID),

   CONSTRAINT FK_ENROLLMENT_STUD FOREIGN KEY (STUD_ID) REFERENCES STUDENTS (STUD_ID),

   CONSTRAINT FK_ENROLLMENT_COURSE FOREIGN KEY (COURSE_ID) REFERENCES COURSES (COURSE_ID)

 ) ENGINE=INNODB DEFAULT CHARSET=LATIN1;

 CREATE TABLE USER_PICS

 (

   ID INT(11) NOT NULL AUTO_INCREMENT,

   NAME VARCHAR(50) DEFAULT NULL,

   PIC BLOB,

   BIO LONGTEXT,

   PRIMARY KEY (ID)

 ) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=LATIN1;

 DROP TABLE IF EXISTS USER_PICS;

 DROP TABLE IF EXISTS COURSE_ENROLLMENT;

 DROP TABLE IF EXISTS COURSES;

 DROP TABLE IF EXISTS TUTORS;

 DROP TABLE IF EXISTS STUDENTS;

 DROP TABLE IF EXISTS ADDRESSES;

 --Sample data for table ADDRESSES

 INSERT INTO ADDRESSES (ADDR_ID,STREET,CITY,STATE,ZIP,COUNTRY) VALUES

  (1,'4891 Pacific Hwy','San Diego','CA','92110','San Diego'),

  (2,'2400 N Jefferson St','Perry','FL','32347','Taylor'),

  (3,'710 N Cable Rd','Lima','OH','45825','Allen'),

  (4,'5108 W Gore Blvd','Lawton','OK','32365','Comanche');

 -- Sample data for table STUDENTS

 INSERT INTO STUDENTS (STUD_ID,NAME,EMAIL,PHONE,DOB,BIO,PIC,ADDR_ID) VALUES

  (1,'Timothy','timothy@gmail.com','123-123-1234','1988-04-25',NULL,NULL,3),

  (2,'Douglas','douglas@gmail.com','789-456-1234','1990-08-15',NULL,NULL,4);

 -- Sample data for table TUTORS

 INSERT INTO TUTORS (TUTOR_ID,NAME,EMAIL,PHONE,DOB,BIO,PIC,ADDR_ID) VALUES

  (1,'John','john@gmail.com','111-222-3333','1980-05-20',NULL,NULL,1),

  (2,'Paul','paul@gmail.com','123-321-4444','1981-03-15',NULL,NULL,2);

 -- Sample data for table courses

 INSERT INTO COURSES (COURSE_ID,NAME,DESCRIPTION,START_DATE,END_DATE,TUTOR_ID) VALUES

  (1,'Quickstart Core Java','Core Java Programming','2013-03-01','2013-04-15',1),

  (2,'Quickstart JavaEE6','Enterprise App Development using JavaEE6','2013-04-01','2013-08-30',1),

  (3,'MyBatis3 Premier','MyBatis 3 framework','2013-06-01','2013-07-15',2);

 -- Sample data for table COURSE_ENROLLMENT

 INSERT INTO COURSE_ENROLLMENT (COURSE_ID,STUD_ID) VALUES

  (1,1),

  (1,2),

  (2,2);

 log4j.rootLogger=INFO, stdout

 log4j.appender.stdout=org.apache.log4j.ConsoleAppender

 log4j.appender.stdout.layout=org.apache.log4j.PatternLayout

 log4j.appender.stdout.layout.ConversionPattern=%d [%-5p] %c - %m%n

 log4j.logger.com.mybatis3=DEBUG

 package com.mybatis3.services;

 import java.util.Date;

 import java.util.List;

 import org.junit.AfterClass;

 import static org.junit.Assert.*;

 import org.junit.BeforeClass;

 import org.junit.Test;

 import com.mybatis3.domain.Student;

 /**

  * @author Siva

  *

  */

 public class StudentServiceTest

 {

     private static StudentService studentService;

     @BeforeClass

     public static void setup()

     {

         studentService = new StudentService();

         TestDataPopulator.initDatabase();

     }

     @AfterClass

     public static void teardown()

     {

         studentService = null;

     }

     @Test

     public void testFindAllStudents()

     {

         List<Student> students = studentService.findAllStudents();

         assertNotNull(students);

         for (Student student : students)

         {

             assertNotNull(student);

             System.out.println(student);

         }

     }

     @Test

     public void testFindStudentById()

     {

         Student student = studentService.findStudentById(1);

         assertNotNull(student);

     }

     @Test

     public void testCreateUStudent()

     {

         Student student = new Student();

         int id = 4;

         student.setStudId(id);

         student.setName("student_"+id);

         student.setEmail("student_"+id+"gmail.com");

         student.setDob(new Date());

         studentService.createStudent(student);

         Student newStudent = studentService.findStudentById(id);

         assertNotNull(newStudent);

         assertEquals("student_"+id, newStudent.getName());

         assertEquals("student_"+id+"gmail.com", newStudent.getEmail());

     }

     @Test

     public void testUpdateStudent()

     {

         int id = 2;

         Student student =studentService.findStudentById(id);

         student.setStudId(id);

         student.setName("student_"+id);

         student.setEmail("student_"+id+"gmail.com");

         Date now = new Date();

         student.setDob(now);

         studentService.updateStudent(student);

         Student updatedStudent = studentService.findStudentById(id);

         assertNotNull(updatedStudent);

         assertEquals("student_"+id, updatedStudent.getName());

         assertEquals("student_"+id+"gmail.com", updatedStudent.getEmail());

     }

 }

 /**

  *

  */

 package com.mybatis3.services;

 import java.io.Reader;

 import java.sql.Connection;

 import org.apache.ibatis.io.Resources;

 import org.apache.ibatis.jdbc.ScriptRunner;

 import org.slf4j.Logger;

 import org.slf4j.LoggerFactory;

 import com.mybatis3.util.MyBatisSqlSessionFactory;

 /**

  * @author Siva

  *

  */

 public class TestDataPopulator

 {

     private static Logger logger = LoggerFactory.getLogger(TestDataPopulator.class);

     public static void main(String[] args) {

         initDatabase();

     }

     public static void initDatabase()

     {

         Connection connection = null;

         Reader reader = null;

         try {

             connection = MyBatisSqlSessionFactory.getConnection();

             ScriptRunner scriptRunner = new ScriptRunner(connection);

             reader = Resources.getResourceAsReader("sql/drop_tables.sql");

             scriptRunner.runScript(reader);

             logger.info("drop_tables.sql executed successfully");

             reader = Resources.getResourceAsReader("sql/create_tables.sql");

             scriptRunner.runScript(reader );

             logger.info("create_tables.sql executed successfully");

             reader = Resources.getResourceAsReader("sql/sample_data.sql");

             scriptRunner.runScript(reader );

             logger.info("sample_data.sql executed successfully");

             connection.commit();

             reader.close();

             scriptRunner.closeConnection();

         } catch (Exception e) {

             throw new RuntimeException(e);

         }

     }

 }