hibernate.cfg.xml

Basic setup

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE hibernate-configuration PUBLIC
  "-//Hibernate/Hibernate Configuration DTD 3.0//EN"
  "http://hibernate.sourceforge.net/hibernate-configuration-3.0.dtd">
<hibernate-configuration>
    <session-factory>
     <property name="hibernate.dialect">org.hibernate.dialect.MySQLDialect</property>
     <property name="connection.driver_class">com.mysql.jdbc.Driver</property>
     <property name="connection.username">root</property>
     <property name="connection.password">root</property>
     <property name="connection.url">jdbc:mysql://192.168.56.101:3306/hibernate</property>
     <mapping resource="com/gvace/domain/Employee.hbm.xml"/>
    </session-factory>
</hibernate-configuration>

<mapping resource="com/gvace/domain/Employee.hbm.xml"/> for mapping to each *.hbm.xml file



Output query for debug: 
 <property name="show_sql">true</property>
<property name="format_sql">true</property>

  <property name="hbm2ddl.auto">

<property name="hbm2ddl.auto">update</property>
Variable values: validate | update | create | create-drop

So the list of possible options are,

• validate: validate the schema, makes no changes to the database.
• update: update the schema, alter or add column, never delete column
• create: creates the schema, destroying previous data, every time when app loads
• create-drop: drop the schema when session factory closed.

Session Range
<property name="hibernate.current_session_context_class"> thread</property>
hibernate.current_session_context_class: jta, thread, managed, custom.Class

jta: cross databases/hosts, remote server database

thread: local events only, for one single database

Character Encoding

  <property name="hibernate.connection.CharSet">utf8</property>
  <property name="hibernate.connection.characterEncoding">utf8</property>

  <property name="hibernate.connection.useUnicode">true</property>

Hibernate

Core Classes and Interfaces

Hibernate API

ORM(Object Relation Mapping)


pojo: plain ordinary java object

A pojo object

1. Has at least a primary key field
2. other fields
3. public get/set functions
4. a non-parameter construct function(for hibernate reflection use)

Configuration :
Load hibernate.cfg.xml
Manage ORM files
Load driver, url, login
Manage hibernate configs

SessionFactory :
Get session instance from SessionFactory
Initialized when application starts
Occupied large memory, only need one instance for each database, so use singleton for each database
Cache sql statements and some data (session level cache)


Session : generated from sessionFactory
http://gvace.blogspot.com/2015/12/hibernate-session.html

Transaction : transaction is required, so remember to commit

Example 

Employee pojo -> employee table

hibernate.cfg.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE hibernate-configuration PUBLIC
  "-//Hibernate/Hibernate Configuration DTD 3.0//EN"
  "http://hibernate.sourceforge.net/hibernate-configuration-3.0.dtd">
<hibernate-configuration>
    <session-factory>
     <property name="hibernate.dialect">org.hibernate.dialect.MySQLDialect</property>
     <property name="connection.driver_class">com.mysql.jdbc.Driver</property>
     <property name="connection.username">root</property>
     <property name="connection.password">root</property>
     <property name="connection.url">jdbc:mysql://192.168.56.101:3306/hibernate</property>
     <property name="show_sql">true</property>
     <mapping resource="com/gvace/domain/Employee.hbm.xml"/>
    </session-factory>
</hibernate-configuration>

Employee.hmb.xml

hmb.xml file: manage relationship between a pojo class and a table

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<!-- Generated Feb 26, 2016 10:59:22 PM by Hibernate Tools 3.4.0.CR1 -->
<hibernate-mapping>
    <class name="com.gvace.domain.Employee" table="employee">
        <id name="id" type="java.lang.Integer">
            <column name="ID" />
            <generator class="increment" />
        </id>
        <property name="name" type="java.lang.String">
            <column name="NAME" not-null="true"/>
        </property>
        <property name="email" type="java.lang.String">
            <column name="EMAIL" not-null="true" />
        </property>
        <property name="hiredate" type="java.util.Date" >
            <column name="HIREDATE" not-null="true"/>
        </property>
    </class>
</hibernate-mapping>

Employee.java

pojo class, require serialize

package com.gvace.domain;

public class Employee implements Serializable{
 private static final long serialVersionUID = -3742562662542696434L;
 private Integer id;
 private String name;
 private String email;
 private java.util.Date hiredate;
 public Integer getId() {
  return id;
 }
 public void setId(Integer id) {
  this.id = id;
 }
 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 java.util.Date getHiredate() {
  return hiredate;
 }
 public void setHiredate(java.util.Date hiredate) {
  this.hiredate = hiredate;
 }
 public Employee() {
 }
}

Example of using the pojo

 @Test
 public void test(){
  //1. Initiate config, load config file
  Configuration configuration = new Configuration().configure("hibernate.cfg.xml");
  //2. Create SessionFactory, normally use singleton
  ServiceRegistry serviceRegistry;
  serviceRegistry = new StandardServiceRegistryBuilder().applySettings(configuration.getProperties()).build();
  SessionFactory sessionFactory = configuration.buildSessionFactory(serviceRegistry);
  //3. Create session
  Session session = sessionFactory.openSession();
  //4. Start a transaction
  Transaction transaction = session.beginTransaction();
  
  //5. Add an employee
  Employee employee = new Employee();
  
  employee.setName("abc");
  employee.setEmail("abc@abc.com");
  employee.setHiredate(new Date());
  
  //6. Save
  session.save(employee);
  
  //7. Commit
  transaction.commit();
  
  //8. Close session
  session.close();
 }

Struts Tags

In java ee standards, there are three types of tags

1. customized tags
2. jstl tags
3. struts tags

*.tld files: tag lib description

for example struts-html.tld, in struts-taglib.jar, defines all html tags

HTML Tags

<%@ taglib prefix="html" uri="http://struts.apache.org/tags-html" %>

<html:image> is a image button

<html:img> is just a image

paramName ="<name-of-the-form-bean-whose-property-you-want-to-use>" 
paramId ="<name-of-the-parameter>" .... in your case it will be paramId="action"
paramProperty="<value-of-the-parameter-which-is-actually-the-value-of-the-property-of-the-form-bean-uhave-specified-above>"

<logic:notpresent> vs <logic;empty>

usually notpresent is used for availability of roles so you can tailor your jsp pages based on roles. But it basically checks for the existance of objects. 

empty is for checking if strings (length of 0) or collections (null) are empty.

<html:form> must map to a ActionForm class!

And each property inside the form must map to a variable in the ActionForm

<html:form action="/login1.do">: action url must NOT include the webapp directory, and this action will automatically validate by struts BEFORE submit the form! If the action url is not reachable, this page will throw error

Bean Tags

For create, visit bean and bean properties.
Can create bean based on cookies, headers, and parameters
<bean:write> <!-- for output info -->
<%
request.setAttribute("abc","hello world");
%>
<bean:write name="abc"/>

<!-- for output value from object, the property other than String must have format configured
Otherwise, it will cause error "Cannot find message resources under key org.apache.struts.action.MESSAGE"
and Error "org.apache.jasper.servlet.JspServletWrapper.handleJspException"
-->
<%
Cat myCat = new Cat();
myCat.setName("ccc");
myCat.setAge(3);
request.setAttribute("myCat", myCat);
%>

<bean:write name="myCat" property="name"/>
<bean:write name="myCat" property="age" format="0"/>

<bean:message>
Normally used for error message
To use <bean:message>, we need to set struts-config.xml with message-resources

1. <message-resources parameter="com.gvace.web.struts.ApplicationResources"></message-resources>
2. create file: com.gvace.web.struts/ApplicationResources.properties
3. ApplicationResources.properties file content example: key1 = welcome {0}
4. use the message: <bean:message key="key1" arg0="Yushan"/>
5. error message example:
   err1 = your error is: {0}{1}
   <bean:message key="err1" arg0="Username is empty" arg1="Password is empty"/>
6. For character encoding in properties file:
   use native2ascii from jdk/bin/, copy the String into native2ascii, it will translate to ascii

<bean:define> (not often used)

<!-- define a bean or its property -->

<bean:define id="kkk" name="abc" value="hello" scope="request"></bean:define>

<!-- jsp way to define a bean or its property -->
<jsp:useBean id="cat2" class="com.gvace.model.Cat"></jsp:useBean>
<jsp:setProperty name="cat2" property="name" value="tom"/>
<jsp:setProperty name="cat2" property="age" value="3"/>
${cat.name} ${cat.age}
<!-- the above tag equals the following code -->
<%
Cat cat2 = new Cat();
cat2.setName("tom");
cat2.setAge(3);
%>
<bean:page>

not introduced

Logic Tags

<logic:iterate>: iterate through collection/array
<%
List<Cat> al = new ArrayList<Cat>();
al.add(cat1);
al.add(cat2);
al.add(cat3);
al.add(cat4);
pageContext.setAttribute("cats", al);
%>

<logic:iterate id="myCat" name="cats">
${myCat.name}, ${myCat.age}<br />
</logic:iterate>

<logic:empty> <logic:notEmpty>

<logic:empty name="cat1">cat1 not exist</logic:empty>
<logic:notEmpty name="cat1">cat1 is exist</logic:notEmpty>

<logic:greaterThan>
<logic:greaterThan name="myCat" value="3" property="age">
cat: <bean:write name="myCat" property="name"/> age bigger than 20
</logic:greaterThan>

Nested Tags(optional)

Tiles Tags(optional)

JSTL

jstl(jsp standard tag library)

Version:
Not lower than JSP1.2 and Servlet 2.3 can use jstl
JSP2.0 can use jstl1.2

1. normal usage tag
2. conditional tag
3. iterator tag
4. url related tag(xml, sql)

Config jstl library

We need two librarys: jstl and taglibs-standard

import

<%@ taglib prefix="c" uri="http://java.sun.com/jstl/core" %>

Four levels, 

1. pageContext.setAttribute("abc", "abc1
2. <c:if test="${empty abc}">
3. abc is empty
4. </c:if>
5. <c:if test="${!empty abc}">
6. abc is not empty
7. </c:if>
");
8. request.setAttribute("abc", "abc2");
9. session.setAttribute("abc", "abc3");
10. application.setAttribute("abc", "abc3");

${pageContext.request.contextPath} is the contextPath I prefer to use, it returns the web app path, as same as request.getContextPath()

empty
detect if value is null, "", empty array, empty map or empty collection
<c:if test="${empty abc}">
abc is empty
</c:if>
<c:if test="${!empty abc}">
abc is not empty

</c:if>

quick condition
${2>1?"bigger":"smaller"}

func(arg,arg1,arg2)
call a function with zero to multiple arguments

<c:out>
<c:out value="${abc}"></c:out>

${} is the mark for that enclosed value is a variable or expression
With the same attribute name, c:out value always looking for the lowest level value first.

<c:out value="${abc}" default="default_value"></c:out> : default value if no attribute found in any level

<c:out value="${abc}"  escapeXml="false"></c:out>
true: make the output to be text only
false: make the output to be html
default is true


<c:out value="${user.name}"></c:out>: user is an object, name is the field of the object
Which equals ((User)request.getAttribute("user")).getName();

<c:set>
<c:set var="abc" value="abc4" scope="request"></c:set>
Do not include ${} in var because it's already variable name, only use ${} when it's for value

<c:remove>
<c:remove var="abc" scope="request"/>
If scope is not set, this will remove all four levels attributes with that variable name

<c:catch>
<c:catch var="myException" >
<% int i=8/0; %>
</c:catch>
<c:out value="${myException}"></c:out><c:out value="${myException.message}"></c:out>
Catch and assign the exception a variable name, so you can catch it latter

<c:if>
<c:if> can only work with single condition
<c:if test="${a=='hello'}">
OK!
</c:if>
<c:if test="${a!='hello'}">
NOT OK!
</c:if>

<c:if test="${age>56}">
older
</c:if>

<c:if test="${age1>10 and age1<56}">
in middle
</c:if>

<br />

<c:choose><c:when><c:otherwise>
Same as:
switch(){
case:
case:
default:
}
<c:choose>
<c:when test="${age>56}">Old</c:when>
<c:when test="${age<29}">Young</c:when>
<c:when test="${age>=29 and age<=56}">Middle</c:when>
<c:otherwise>Age not available</c:otherwise>
</c:choose>


<c:forEach>

<%
ArrayList<User> list = new ArrayList<User>();
list.add(new User("aaa",19));
list.add(new User("bbb",29));
list.add(new User("ccc",39));
pageContext.setAttribute("list", list);
%>
1.Simple forEach
<c:forEach items="${list}" var="user">
<c:out value="${user.name}" />
<c:out value="${user.age}" />
<br />
</c:forEach>
2. iterate fix times, like for loop
<c:forEach var="i" begin="1" end="10">
<c:out value="${i}" />
</c:forEach>
3. iterate fix times with number of steps, like for loop
instance 1 can use `step` also
<c:forEach var="i" begin="1" end="10" step="3">
<c:out value="${i}" />
</c:forEach>
4. can iterator through map or set
<c:forEach items="${map}" var="entry">
key=${entry.key},value=${entry.value}
</c:forEach>

<c:forEach items="${set}" var="value">
value=${value}
</c:forEach>
5. nested level also work fine
<c:forEach items="${set}" var="value">
name=${value.person.name}
departmentName=${value.department.name}
</c:forEach>

<c:forTokens>
<c:forTokens items="a,b,c,d,e,f" begin="0" end="2" step="2" delims="," var="value">
<c:out value="${value}" />
</c:forTokens>

<c:redirect>
<c:redirect url="http://www.google.com"></c:redirect>

<c:import>
import other pages, can also pass parameters, use ${param.name} to get passed value
<c:import url="/WEB-INF/a.jsp">
<c:param name="name" value="abc"></c:param>
</c:import>

Struts1 Config more

Load Struts1, config web.xml

  <servlet>
    <servlet-name>action</servlet-name>
    <servlet-class>org.apache.struts.action.ActionServlet</servlet-class>
    <init-param>
      <param-name>config</param-name>
      <param-value>/WEB-INF/struts-config.xml</param-value>
    </init-param>
    <load-on-startup>2</load-on-startup>
  </servlet>
  <servlet-mapping>
    <servlet-name>action</servlet-name>
    <url-pattern>*.do</url-pattern>
  </servlet-mapping>

actionServlet can assign multiple config files, separate by comma:

    <init-param>
      <param-name>config</param-name>
      <param-value>/WEB-INF/struts-config.xml,/WEB-INF/struts-config1.xml,</param-value>
    </init-param>

In Struts1, Each Action is a Servlet, and each Servlet is singleton

Action scope: Default is session

• request: request.setAttribute("UserForm",userForm); //auto runs by Struts1
• session: request.getSession().setAttribute("UserForm",userForm); //auto runs by Struts1

 


<action-mappings>
 <!-- localhost:8080/webapp/login.do, action path is the requested url path -->
 <!-- form-bean connect to the action identified by name -->
 <action path="/login" name="UserForm" scope="request" type="com.gvace.struts1login.actions.LoginAction">
  <!-- action choose which forward to go by forward name -->
  <forward name="ok" path="/WEB-INF/welcome.jsp"></forward>
  <forward name="err" path="/WEB-INF/err.jsp"></forward>
 </action>
</action-mappings>

Character encoding problem on Struts

ActionServlet automatically fills the actionForm, there is no way to set character encoding after this.

We have to do character encoding before calling ActionServlet.
So we have to use <filter> before ActionServlet.

@WebFilter(filterName="/CharacterEncodingFilter", urlPatterns="/*")
public class CharacterEncodingFilter implements Filter {

    /**
     * Default constructor. 
     */
    public CharacterEncodingFilter() {
        // TODO Auto-generated constructor stub
    }

 /**
  * @see Filter#destroy()
  */
 public void destroy() {
  // TODO Auto-generated method stub
 }

 /**
  * @see Filter#doFilter(ServletRequest, ServletResponse, FilterChain)
  */
 public void doFilter(ServletRequest request, ServletResponse response, FilterChain chain) throws IOException, ServletException {
  request.setCharacterEncoding("utf-8");
  response.setContentType("text/html;charset=utf-8");
  chain.doFilter(request, response);
 }

 /**
  * @see Filter#init(FilterConfig)
  */
 public void init(FilterConfig fConfig) throws ServletException {
  // TODO Auto-generated method stub
 }

}

Struts1 internal mechanism with an example to use

Seeing the diagram from here

To understand Struts1 internal mechanism, we will do manual setup and config for one web login request.

1. ActionServlet: core servlet of Struts, is the call center of struts
2. web.xml: Config web.xml to load ActionServlet as a normal servlet(the entrance of struts framework)
        Assign ActionServlet's own config file in <init-param>: struts-config.xml
        In this config, all *.do url requests will be forward to use structs framework
       The ActionServlet is important to be initialized before the first call, so:
   <load-on-startup>2</load-on-startup> is important to set, when you see error :  Module 'null' not found. 

3.   <servlet>
      <servlet-name>action</servlet-name>
      <servlet-class>org.apache.struts.action.ActionServlet</servlet-class>
      <!-- struts-config -->
      <init-param>
       <param-name>config</param-name>
       <param-value>/WEB-INF/struts-config.xml</param-value>
      </init-param>
      <load-on-startup>2</load-on-startup>
     </servlet>
     <servlet-mapping>
      <servlet-name>action</servlet-name>
      <url-pattern>*.do</url-pattern>
     </servlet-mapping>
   

4. struts-config.xml: ActionServlet load this file to config all actions and actionForms
   (a)action's path will be the requested url's resource part(take the string ".do" out)
   (b)<form-bean>'s name will be used to match <action>'s name
   (c)<forward> name will be used to forward to target output file
   
   

   <?xml version="1.0" encoding="UTF-8"?>
   <!DOCTYPE struts-config PUBLIC "-//Apache Software Foundation//DTD Struts Configuration 1.3//EN"
          "http://struts.apache.org/dtds/struts-config_1_3.dtd">
   <struts-config>
    <form-beans>
     <form-bean name="UserForm" type="com.gvace.struts1login.forms.UserForm"></form-bean>
    </form-beans>
    <action-mappings>
     <!-- localhost:8080/webapp/login.do, action path is the requested url path -->
     <!-- form-bean connect to the action identified by name -->
     <action path="/login" name="UserForm" type="com.gvace.struts1login.actions.LoginAction">
      <!-- action choose which forward to go by forward name -->
      <forward name="ok" path="/WEB-INF/welcome.jsp"></forward>
      <forward name="err" path="/WEB-INF/err.jsp"></forward>
     </action>
    </action-mappings>
   </struts-config>
   

5. Form-bean(UserForm.java in the example), extends ActionForm, is a javabean
   Set all field names match to the form attributes
6. Action class(LoginAction.java in the example),extends Action, contains the logic to process the Form
   Override the ActionForward function to process all logic

    @Override
    public ActionForward execute(ActionMapping mapping, ActionForm form, HttpServletRequest request, HttpServletResponse response)
      throws Exception {
     UserForm userForm = (UserForm)form;
     if(Auth.succeed(form.getUsername(),form.getPassword())){
      return mapping.findForward("ok");
     }
     return mapping.findForward("err");
    }
   

7. ActionMapping: use ActionMapping to tell ActionServlet which path to forward, by telling it the forward name, using findForward function: mapping.findForward("ok");

SORM

Simple Object-relational mapping (SORM)

A simple light weight SORM framework, originally learned from a tutorial https://www.youtube.com/playlist?list=PLTstZD3AK3S-eVO1jay1EURUVHG20_5dq

GitHub:  https://github.com/ylu999/SORM

So far, I just implemented MySQL, but Oracle should be easy to implement like MySQL.
To implement other SQLs like Oracle, create new class similar to these two.

• MySQLQuery.java
• MySQLConvertor.java

Wine apps in Ubuntu text font adjust

in /home/yushan/.wine/system.reg

change the LogPixels

[System\\CurrentControlSet\\Hardware Profiles\\Current\\Software\\Fonts] 1447611568
"FIXEDFON.FON"="vgafix.fon"
"FONTS.FON"="vgasys.fon"
"LogPixels"=dword:00000095
"OEMFONT.FON"="vgaoem.fon"


Then run command:

wineserver -k
wineboot

Properties

Example:

public class DBConnector {
 private static String url = null;
 private static String username = null;
 private static String password = null;
 private static String driver = null;
 static{
  Properties pp = new Properties();
  try {
   //pp.load(DBConnector.class.getClassLoader().getResourceAsStream("dbinfo.properties"));
   pp.load(Thread.currentThread().getContextClassLoader().getResourceAsStream("dbinfo.properties")); // this one is better
  } catch (IOException e) {
   //e.printStackTrace();
   throw new RuntimeException(e);
  }
  username = pp.getProperty("username");
  password = pp.getProperty("password");
  driver = pp.getProperty("driver");
  url = pp.getProperty("url");;
 }
 //Other functions
}

For character encoding in properties file: use native2ascii from jdk/bin/, copy the String into native2ascii, it will translate to ascii

CLOB vs BLOB

CLOB:  Character Large Object(LONGTEXT/TEXT in MySQL)
BLOB:  Binary Large Object(BLOB in MySQL)

Example code:

@Test
public void writingCLOB_BLOG() {
 Connection conn = null;
 PreparedStatement ps = null;
 try {
  Class.forName("com.mysql.jdbc.Driver");
  conn = DriverManager.getConnection(
    "jdbc:mysql://192.168.56.101:3306/javaweb", "root", "root");
  ps = conn.prepareStatement("INSERT INTO `javaweb`.`users` "
    + "(`username`, `password`, `email`, `grade`, `usertext`, `headimg`) "
    + "VALUES (?,?,?,?,?,?);");
  ps.setString(1, "abcd5");
  ps.setString(2, "abc");
  ps.setClob(
    3,
    new BufferedReader(new InputStreamReader(
      new ByteArrayInputStream("aaaaaaaaa@gvace.com"
        .getBytes()))));  //put a string as stream, into clob
  ps.setInt(4, 1);
  ps.setClob(5, new FileReader("/home/yushan/a.log")); //a file as stream, into clob
  ps.setBlob(6, new FileInputStream("/home/yushan/Pictures/db_clob_blob.png")); //a file as stream, into blob
  ps.executeUpdate();
 } catch (ClassNotFoundException e) {
  e.printStackTrace();
 } catch (SQLException e) {
  e.printStackTrace();
 } catch (FileNotFoundException e) {
  e.printStackTrace();
 }
 finally{
  //Close all resources
 }
}
@Test
public void readingCLOB_BLOG() {
 Connection conn = null;
 PreparedStatement ps = null;
 ResultSet rs = null;
 Reader r = null;
 InputStream is = null;
 OutputStream os = null;
 try {
  Class.forName("com.mysql.jdbc.Driver");
  conn = DriverManager.getConnection(
    "jdbc:mysql://192.168.56.101:3306/javaweb", "root", "root");
  ps = conn.prepareStatement("SELECT * FROM `javaweb`.`users` "
    + "WHERE id=?;");
  ps.setInt(1, 34);
  rs = ps.executeQuery();
  while(rs.next()){
   Clob c = rs.getClob("usertext");
   r = c.getCharacterStream();
   char[] buf = new char[1024];
   int len = -1;
   while((len =r.read(buf))>0){
    System.out.print(new String(buf));
   }
   
   Blob b = rs.getBlob("headimg");
   is = b.getBinaryStream();
   os = new FileOutputStream("/home/yushan/Pictures/db_clob_blob_copy.png");
   len = -1;
   byte[] buf1 = new byte[1024];
   while((len = is.read(buf1))>0){
    //System.out.print(new String(buf1));
    os.write(buf1);
   }
  }
  
 } catch (ClassNotFoundException e) {
  e.printStackTrace();
 } catch (SQLException e) {
  e.printStackTrace();
 } catch (FileNotFoundException e) {
  e.printStackTrace();
 } catch (IOException e) {
  e.printStackTrace();
 }
 finally{
  //Close all resources
 }
}

Date vs Time vs Timestamp in SQL

java.util.Date

Direct Known Subclasses:

Date, Time, Timestamp

• java.sql.Date         year,month,day
• java.sql.Time         hour,minute,second
• java.sql.Timestamp         year,month,day,hour,minute,second

long represents time in milliseconds, starts from 1970/1/1 00:00:00

Date, time utils: http://gvace.blogspot.com/2016/02/dateformat.html

Example for date into sql:

 
 PreparedStatement ps = con.prepareStatement(sql);

 java.sql.Date date = new java.sql.Date(System.currentTimeMillis());
 ps.setObject(3, date);

 java.sql.Timestamp timestamp = new java.sql.Timestamp(System.currentTimeMillis());
 ps.setObject(4, timestamp);
 ps.execute();

Example for resultSet to date:

 
 java.sql.Time time = rs.getTime("ts");
 java.sql.Timestamp timestamp = rs.getTimestamp("ts");
 java.util.Date date = rs.getDate("ts");

DateFormat

SimpleDateFormat:

public long str2Date(String dateString){
 public java.util.Date str2Date(String dateString,String formatString){
  DateFormat format = new SimpleDateFormat(formatString);
  try{
   java.util.Date date = format.parse(dateString);
   return date;
  }catch(ParseException e){
   e.printStackTrace();
   return null;
  }
 }
 public long str2Time(String dateString,String formatString){
  java.util.Date date = str2Date(dateString, formatString);
  if(date==null)return 0;
  return date.getTime();
 }
 @Test
 public void testDate(){
  String date1 = "2016-02-03 00:21:33";
  String format1 = "yyyy-MM-dd hh:mm:ss";
  long time = str2Time(date1, format1);
  System.out.println(System.currentTimeMillis() - str2Time(date1, format1));
 }

Transaction

DML: INSERT, UPDATE, DELETE
DDL: CREATE TABLE, ALTER TABLE
DCL: GRANT

Transaction starts

• when the first DML runs.

Transaction ends:

• run commit() or rollback
• when run a DDL, will trigger commit()
• when run a DCL, will trigger commit()
• when database disconnect
• when run a DML, but the DML failed. Then it triggers rollback

ACID

• Atomicity: all succeed or all die
• Consistency: if error, must rollback to exactly original
• Isolation: other transactions will only see either before/after the change, cannot see in the middle
• Durability: if succeed, it will affect permanently

Isolation 4 levels(from low to high, but efficiency is from high to low)

1. Read Uncommitted
2. Read Committed(commonly used)
3. Repeatable Read
4. Serializable(lock table)

Example of Transaction

@Test
public void test(){
 List<String> SQLs = new ArrayList<String>();
 List<Object[]> parameters = new ArrayList<Object[]>();
 String sql1 = "INSERT INTO `javaweb`.`users` (`username`, `password`, `email`) VALUES (?,?,?);";
 Object[] parameters1 = new Object[]{"abcd1", "abc", "abc"};
 String sql2 = "INSERT INTO `javaweb`.`users` (`username`, `password`, `email`) VALUES (?,?,?);";
 Object[] parameters2 = new Object[]{"abcd2", "abc", "abc"};
 SQLs.add(sql1);
 SQLs.add(sql2);
 parameters.add(parameters1);
 parameters.add(parameters2);
 executeQuery(SQLs, parameters);
}
public void executeQuery(List<String> SQLs,List<Object[]> parameters){
 try {
  Class.forName("com.mysql.jdbc.Driver");
 } catch (ClassNotFoundException e) {
  return;
 }
 try(Connection conn = DriverManager.getConnection("jdbc:mysql://192.168.56.101:3306/javaweb","root","root");){
  conn.setAutoCommit(false);
  for(int i=0;i<SQLs.size();i++){
   try (
    PreparedStatement ps = createPreparedStatement(conn,SQLs.get(i),parameters.get(i));
   ) {
    int resultCount = ps.executeUpdate();
    System.out.println("Update succeed, updated result count:"+resultCount);
   } catch (SQLException e) {
    e.printStackTrace();
    conn.rollback();
    return;
   }
   try {
    Thread.sleep(6000);
   } catch (InterruptedException e) {
    e.printStackTrace();
   }
  }
  conn.commit();
 } catch (SQLException e) {
  
 }
 return;
}

Batch

Batch:
For large amount of sql running in one time

Suggest using Statement, because PreparedStatement has limitation for big inputs, which may cause exceptions

Remember to setAutoCommit(false), then call commit() at the end

  
 DataSource ds = DBConnector.setupDataSource();
 try(
  Connection conn = ds.getConnection();
  Statement stmt = conn.createStatement();
 ){
  conn.setAutoCommit(false);
  long start = System.currentTimeMillis();
  for(int i=0;i<20000;i++){
   stmt.addBatch("INSERT INTO users(username,pwd,ts) values('a"+i+"','pass',now())");
  }
  stmt.executeBatch();
  conn.commit();
  long end = System.currentTimeMillis();
  System.out.println("Spend time:"+(end-start)+"ms"); //see how fast
 } catch (SQLException e) {
  Logger lgr = Logger.getLogger(SqlHelper.class);
  lgr.log(Level.WARNING,e.getMessage(),e);
 }

JDBC

Class.forName("com.mysql.jdbc.Driver");

Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test","root","root");

Closing Resources

Before Java 6 Closing sequence:

1. ResultSet
2. Statement
3. Connection

Separate the close in different try/catch

From Java 7

Simply do try with resources, the resources will auto closed.

 DataSource ds = DBConnector.setupDataSource();
  
 try (
  Connection conn = ds.getConnection();
  PreparedStatement ps = createPreparedStatement(conn,sql,parameters);
  ResultSet rs = ps.executeQuery();
 ) {
  CachedRowSet rowset = new CachedRowSetImpl();
  rowset.populate(rs);
  return rowset;
 } catch (SQLException e) {
  Logger lgr = Logger.getLogger(SqlHelper.class);
  lgr.log(Level.WARNING,e.getMessage(),e);
 }

Three interface

• Statement: basic sql, no parameter accepted
• PreparedStatement: create by prepareStatement
• CallableStatement: create by prePareCall

Basic usage:
String sql = "SELECT * FROM abc WHERE name=?";
PreparedStatement ps = con.prepareStatement(sql);
ps.setObject(1, "bar"); //element index starts from 1

More usage:
PreparedStatement ps = con.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);
//this will return the inserted record's primary key

To get the key:
ps.executeUpdate();
try(ResultSet rs = ps.getGeneratedKeys();){
   rs.next();
   key = rs.getInt(1);
}

Statement functions

• execute(): return true/false, for if there is result
• executeQuery(): run select sqls, return ResultSet
• executeUpdate(): run insert/update/delete, return changed rows number

ResultSet

• next(): starts from null, call rs.next() to get first record
• getInt(), getString()