Neusoft Training-002

Database operations

Entity Bean

Package Org.mo.model;public class Usermodel {private Integer id;private String name;private integer age;public integer Get ID () {return ID;} public void SetId (Integer id) {this.id = ID;} Public String GetName () {return name;} public void SetName (String name) {this.name = name;} Public Integer Getage () {return age;} public void Setage (Integer age) {this.age = age;}}

Query class

Package Org.mo.model;public class Userquerymodel extends Usermodel {private integer age2;public integer getAge2 () {return Age2;} public void SetAge2 (Integer age2) {this.age2 = Age2;}}

Connection database Operation class

package org.mo.uitl;import java.sql.sqlexception;import javax.sql.datasource;import  org.apache.commons.dbcp.basicdatasource;public class connectionutils {private  Basicdatasource basicdatasource = new basicdatasource ();p rivate connectionutils ()  {basicdatasource.setdriverclassname ("Org.gjt.mm.mysql.Driver"); Basicdatasource.seturl ("jdbc:mysql:// localhost:3306/test?useunicode=true&characterencoding=gb2312 "); Basicdatasource.setusername (" root "); Basicdatasource.setpassword ("root");} Public void close ()  throws sqlexception {basicdatasource.close ();} /** *  Inner class  *  *  @author  Administrator *  */private  static class singletonholder {//a hungry man type private static connectionutils intance =  new connectionutils ();} Public static connectionutils getintance ()  {return singletonholder.intance;} PubliC datasource getdatasource ()  {return basicdatasource;}} 

DAO

package org.mo.dao;import java.sql.connection;import java.sql.preparedstatement;import  java.sql.resultset;import java.sql.sqlexception;import java.util.arraylist;import  java.util.list;import org.mo.model.usermodel;import org.mo.model.userquerymodel;import  Org.mo.uitl.connectionutils;public class userjdbcdao {public void create (UserModel  usermodel)  {Connection connection = null;try {connection =  Connectionutils.getintance (). Getdatasource (). getconnection (); final string sql= "INSERT INTO  tbl_user (id, name, age)  values (?,?,?) "; Preparedstatement ps = connection.preparestatement (SQL);p S.setint (1, usermodel.getid ()); Ps.setstring (2, usermodel.getname ());p S.setint (3, usermodel.getage ());p S.execute ();p s.close ();  catch  (exception e)  {}finally{try {connection.close ();}  catch  (Sqlexception e)  {e.printstacktrace ();}}} Public void upate (Usermodel usermodel) {connection connection = null;try { Connection = connectionutils.getintance (). Getdatasource (). getconnection (); Final String SQL = "update tbl_user set name = ?, age = ? where id =  ? "; Preparedstatement ps = connection.preparestatement (SQL);p s.setstring (1, userModel.getName ( ));p S.setint (2, usermodel.getage ());p S.setint (3, usermodel.getid ());p S.execute ();p s.close ();  catch  (exception e)  {}finally{try {connection.close ();}  catch  (sqlexception e)  {e.printstacktrace ();}} Public void delete (integer id) {connection connection = null;try {connection  = connectionutils.getintance (). Getdatasource (). getconnection (); final string sql= "DELETE  tbl_user where id = ? "; Preparedstatement ps = connection.preparestatement (SQL);p s.setint (1, id);p s.execute (); Ps.close ();}  catch  (exception e)  {}finally{try {connection.close ();}  catch  (sqlexception e)  {e.printstacktrace ();}} Public usermodel getsingle (Integer id) {connection connection = null; Usermodel usermodel = null;try {connection = connectionutils.getintance (). Getdatasource (). getconnection (); final string sql= "Select id,name,age from tbl_user  where id = ? "; Preparedstatement ps = connection.preparestatement (SQL);p s.setint (1, id); Resultset executequery = ps.executequery (); if (Executequery.next ()) {usermodel =  Rsmodel (executeQuery);} Executequery.close ();p s.close ();}  catch  (exception e)  {}finally{try {connection.close ();}  catch  (sqlexception e)  {e.printstAcktrace ();}} Return usermodel;} Public list<usermodel> getall () {connection connection = null; List<usermodel> usermodels = new arraylist<usermodel> (); try {connection  = connectionutils.getintance (). Getdatasource (). getconnection (); final string sql= "Select  id,name,age from tbl_user "; Preparedstatement ps = connection.preparestatement (SQL); Resultset executequery = ps.executequery (); if (Executequery.next ()) {Usermodel usermodel  = rsmodel (ExecuteQuery); Usermodels.add (Usermodel);} Executequery.close ();p s.close ();}  catch  (exception e)  {}finally{try {connection.close ();}  catch  (sqlexception e)  {e.printstacktrace ();}} Return usermodels;} Private usermodel rsmodel (resultset executequery)  throws sqlexception {usermodel  usermodel = new usermodel (); int iNt1 = executequery.getint ("id"); String string = executequery.getstring ("name"); Int int2 = executequery.getint (" Age "); Usermodel.setid (int1); Usermodel.setname (string); Usermodel.setage (int2); Return usermodel;} Private string genwhere (Userquerymodel userquserymodel)  {stringbuffer stringbuffer  = new stringbuffer (); if (Userquserymodel.getid ()  > 0) {stringbuffer.append ("  and id = ?  ");} if  (Userquserymodel.getname (). Trim (). Length ()  > 0| |  userquserymodel.getname ()  != null)  {stringbuffer.append (" and name like  ?  ");} if (Userquserymodel.getage ()  > 0 ) {stringbuffer.append ("&NBSP;AND&NBSP;AGE&NBSP;&GT;=&NBSP;?   ");} if (Userquserymodel.getage2 ()  > 0 ) {stringbuffer.append ("&NBSP;AND&NBSP;AGE&NBSP;&LT;=&NBSP;?   ");} Return stringbuffer.tostring ();} Private void preparEPs (USERQUERYMODEL&NBSP;UQM,&NBSP;PREPAREDSTATEMENT&NBSP;PS)  throws exception {int count  = 1;if  (Uqm.getid ()  > 0)  {ps.setint (Count++, uqm.getid ());} if  (Uqm.getname ()  != null && uqm.getname (). Trim (). Length ()  > 0)  {ps.setstring (Count++, uqm.getname ());} if  (Uqm.getage ()  > 0)  {ps.setint (Count++, uqm.getage ());} if  (Uqm.getage2 ()  > 0)  {ps.setint (Count++, uqm.getage2 ());}} Public list<usermodel> getbycondition (Userquerymodel model) {Connection connection  = null; List<usermodel> usermodels = new arraylist<usermodel> (); try {connection  = connectionutils.getintance (). Getdatasource (). getconnection (); final string sql= "Select  id,name,age FROM tbl_user WHERE 1=1  " + genwhere (model)  + "  order BY id  "; Preparedstatement ps = connection.preparestatement (SQL);p repareps (MODEL,&NBSP;PS); Resultset executequery = ps.executequery (); if (Executequery.next ()) {Usermodel usermodel  = rsmodel (ExecuteQuery); Usermodels.add (Usermodel);} Executequery.close ();p s.close ();}  catch  (exception e)  {}finally{try {connection.close ();}  catch  (sqlexception e)  {e.printstacktrace ();}} Return usermodels;}}

Jar Package

Mysql-connector-java-5.1.8-bin.jar

Commons-pool.jar

Commons-dbcp.jar

Neusoft Training-002