Database deletion and modification and optimization

Create a project, import the required jar package, this example uses MySQL database, Navicat Visualizer, using open source C3P0 connection pool, using unit test, structure as follows:

The table used in the example:

There are many drawbacks to getting connections directly from your application:

Using database connection pooling greatly improves program performance: The database connection pool is responsible for allocating, managing, and freeing the database connection, which allows the application to reuse an existing database connection instead of re-establishing a

Here is the Code section:

Studentpo class:

Package po;
Import Java.util.Date;
public class Studentpo
{
Private String Sno;
private String name;
private char sex;
Private Date birthday;
Public Studentpo (String sno, string name, char sex, Date birthday) {
This ();
This.sno = Sno;
THIS.name = name;
This.sex = sex;
This.birthday = Birthday;
}
Public Studentpo ()
{
Super ();
TODO auto-generated Constructor stub
}

Public String Getsno ()
{
Return SNO;
}

public void Setsno (String Sno)
{
This.sno = Sno;
}
Public String GetName ()
{
return name;
}

public void SetName (String name)
{
THIS.name = name;
}
Public Char Getsex ()
{
return sex;
}
public void Setsex (char sex)
{
This.sex = sex;
}
Public Date Getbirthday ()
{
return birthday;
}
public void Setbirthday (Date birthday)
{
This.birthday = Birthday;
}
}

Studentdaoi class:

Package DAO;

Import java.util.List;

Import Po. STUDENTPO;

public interface Studentdaoi
{
public boolean addstudent (STUDENTPO student);
public boolean delstudent (String Sno);
public boolean updstudent (STUDENTPO student);
Public Studentpo Getstudentbysno (String Sno);
Public list<studentpo> getstudents ();
}

Dbutil class:

Package Dao.impl;

Import java.beans.PropertyVetoException;
Import java.sql.Connection;
Import java.sql.PreparedStatement;
Import Java.sql.ResultSet;
Import java.sql.SQLException;
Import java.util.List;

Import Com.mchange.v2.c3p0.ComboPooledDataSource;

Public abstract class Dbutil {
C3P0 Connection pool (optimized performance)
private static Combopooleddatasource DataSource;

static {
String url = "Jdbc:mysql://localhost:3306/db1?useunicode=true&characterencoding=utf-8";
String userName = "root";
String password = "sxh";
String Driver = "Com.mysql.jdbc.Driver";
DataSource = new Combopooleddatasource ();
try {
Datasource.setdriverclass (driver);
Datasource.setjdbcurl (URL);
Datasource.setuser (UserName);
Datasource.setpassword (password);
Datasource.setmaxpoolsize (30);
Datasource.setminpoolsize (10);
Datasource.setmaxstatements (10000);
} catch (Propertyvetoexception e) {
E.printstacktrace ();
}
}

public static Connection Getconn () {
try {
return Datasource.getconnection ();
} catch (SQLException e) {
E.printstacktrace ();
}
return null;
}

//Singleton mode
/*
* private static Connection conn = null; static {//class executes String URL when loading =
* "Jdbc:mysql://localho St:3306/db1?useunicode=true&characterencoding=utf-8 "
*; String userName = "root"; String password = "sxh";
*
*//simpledateformat SDF = new SimpleDateFormat ("Yyyy/mm/dd");//
* No format error: Incorrect date value: ' Sat no V 21:27:48 gmt+08:00 ' try {
* class.forname ("Com.mysql.jdbc.Driver"); conn =
* Drivermanager.getconnection (Url,username,password); } catch (SQLException
* e) {//TODO auto-generated catch block E.printstacktrace ();} catch
* (CLASSNOTFOUNDEXCEP tion e) {//TODO auto-generated catch block
* E.printstacktrace ();}}
*/

public static Boolean cud (String sql, list<object> params) {
Boolean flag = false;
PreparedStatement pstat = null; Preprocessing statements
Connection conn = null;
Try
{
conn = Datasource.getconnection ();
pstat = conn.preparestatement (sql);

if (params!=null)
{
for (int idx=0;idx<params.size (); idx++)
{
Pstat.setobject (idx+1, Params.get (idx));
}
}

int result = Pstat.executeupdate ();
Flag = (result>0)? True:false;
}catch (Exception e) {
E.printstacktrace ();
}
TODO auto-generated Method Stub
SYSTEM.OUT.PRINTLN (flag);
return flag;
}

public static void Close (ResultSet rs,preparedstatement pstat,connection conn)
{
Try
{
if (rs!=null) rs.close ();
if (pstat!=null) pstat.close ();
if (conn!=null) conn.close ();
}
catch (Exception e)
{
E.printstacktrace ();
}
}
}

Studentdaoimpl class:

Package Dao.impl;

Import java.sql.Connection;
Import java.sql.PreparedStatement;
Import Java.sql.ResultSet;
Import java.util.ArrayList;
Import java.util.List;

Import Po. STUDENTPO;
Import DAO. Studentdaoi;

public class Studentdaoimpl extends Dbutil implements Studentdaoi {
public boolean addstudent (Studentpo student) {
String sql = "INSERT into t_student values (?,?,?,?)";
list<object> params = new arraylist<object> ();
Params.add (Student.getsno ());
Params.add (Student.getname ());
Params.add (Student.getsex () + "");
Params.add (Student.getbirthday ());
System.out.println (Student.getclass ());
Use of static methods, which can be invoked by the class name or object name
SYSTEM.OUT.PRINTLN (Dbutil.cud (SQL, params));
return Dbutil.cud (SQL, params);
}

public boolean delstudent (String Sno) {
String sql = "Delete from t_student where Sno =?";
list<object> params = new arraylist<object> ();
Params.add (SNO);
return Dbutil.cud (SQL, params);
}

public boolean updstudent (Studentpo student) {
SimpleDateFormat SDF = new SimpleDateFormat ("Yyyy/mm/dd");
String sql = "Update t_student set name=?,sex=?,birthday=?" where sno=? ";
list<object> params = new arraylist<object> ();
The four parameters are added in the same order as the SQL statement's parameter order
Params.add (Student.getname ());
Params.add (Student.getsex () + "");
Params.add (Student.getbirthday ());
Params.add (Student.getsno ());
SYSTEM.OUT.PRINTLN (Dbutil.cud (SQL, params));
return Dbutil.cud (SQL, params);
}

Public Studentpo Getstudentbysno (String Sno) {
String sql = "SELECT * from t_student where Sno =?";
list<object> params = new arraylist<object> ();
Params.add (SNO);
List<studentpo> students = gets (sql, params);
Return (students.size () > 0)? Students.get (0): null;
}

Public list<studentpo> getstudents () {
String sql = "SELECT * from T_student";
return gets (SQL, NULL);
}

Private list<studentpo> gets (String sql, list<object> params) {
list<studentpo> students = new arraylist<studentpo> ();

Connection conn = Dbutil.getconn ();
PreparedStatement pstat = null;
ResultSet rs = null;
try {
pstat = conn.preparestatement (sql);

if (params! = null) {
for (int idx = 0; idx < params.size (); idx++) {
Pstat.setobject (idx + 1, params.get (IDX));
}
}

rs = Pstat.executequery ();

STUDENTPO student = null;
Use reflection to achieve universal
while (Rs.next ()) {
Student = new Studentpo ();
Student.setsno (rs.getstring ("Sno"));
Student.setname (rs.getstring ("name"));
Student.setsex (rs.getstring ("Sex"). CharAt (0));
Student.setbirthday (rs.getdate ("Birthday"));
Students.add (student);
}
} catch (Exception e) {
E.printstacktrace ();
} finally {
Dbutil.close (RS, PSTAT, conn);
}
return students;
}
}

Appmain class:

Package app;

Import Java.util.Date;
Import Org.junit.Test;

Import DAO. Studentdaoi;
Import Dao.impl.StudentDAOImpl;

Import Po. STUDENTPO;

public class Appmain
{
Private Studentdaoi Studentdao;

Public Appmain ()
{
Studentdao = new Studentdaoimpl ();
}
@Test
public void Testaddstudent ()
{
STUDENTPO student = new Studentpo ("2", "Carina Lau", ' m ', New Date ());
Studentdao.addstudent (student);
}

@Test
public void Testdelstudent ()
{
Studentdao.delstudent ("1");
}

@Test
public void Testupdstudent ()
{
STUDENTPO student = new Studentpo ("2", "Starfish tert", ' m ', New Date ());
Studentdao.updstudent (student);
}

@Test
public void Testgetstudentbysno ()
{
STUDENTPO student = Studentdao.getstudentbysno ("s001");
System.out.println (Student.getname ());
}

@Test
public void Testgetstudents ()
{
list<studentpo> students = studentdao.getstudents ();
System.out.println (Students.size ());
}

}

For unit Testing

Code Address:

Http://pan.baidu.com/s/1ciRPcq

Database deletion and modification and optimization