java jdbc

mysql表：

DROP TABLE IF EXISTS `test`;
CREATE TABLE `test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(255) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `birthday` date DEFAULT NULL,
  `begintime` time DEFAULT NULL,
  `begindatetime` datetime DEFAULT NULL,
  `issupper` bit(1) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8;

DataSourceManager

package cn.byref.demo.jdbc;

import java.beans.PropertyVetoException;
import java.sql.Connection;
import java.sql.SQLException;

import com.mchange.v2.c3p0.ComboPooledDataSource;

public class DataSourceManager {
    public static final String URL = "jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf-8";
    public static final String USERNAME = "root";
    public static final String PASSWORD = "xxx";

    public static Connection getConnection() throws PropertyVetoException, SQLException{
        ComboPooledDataSource ds = new ComboPooledDataSource();
        ds.setJdbcUrl(URL);
        ds.setUser(USERNAME);
        ds.setPassword(PASSWORD);
        ds.setDriverClass("com.mysql.jdbc.Driver");

        Connection conn = ds.getConnection();
        return conn;
    }
}

插入数据测试

package cn.byref.demo.jdbc;

import java.beans.PropertyVetoException;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Time;
import java.sql.Timestamp;
import java.util.Calendar;

import com.mchange.v2.c3p0.ComboPooledDataSource;

public class InsertTest {
	public static void main(String[] args) {
		try {
			insert();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (PropertyVetoException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	
	
	public static final String URL = "jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf-8";
	public static final String USERNAME = "root";
	public static final String PASSWORD = "xxx";

	public static void insert() throws SQLException, PropertyVetoException {
		ComboPooledDataSource ds = new ComboPooledDataSource();
		ds.setJdbcUrl(URL);
		ds.setUser(USERNAME);
		ds.setPassword(PASSWORD);
		ds.setDriverClass("com.mysql.jdbc.Driver");

		Connection conn = ds.getConnection();
		String sql = "insert into test (username,age,birthday,begintime,begindatetime,issupper) "
				+ "values(?,?,?,?,?,?)";
		PreparedStatement pstmt = conn.prepareStatement(sql);
		pstmt.setString(1, "王五");
		pstmt.setInt(2, 20);
		System.out.println(Calendar.getInstance().getTime());
		
		Date d = new Date(Calendar.getInstance().getTime().getTime());
		System.out.println(d);
		pstmt.setDate(3, d);
		Time t = new Time(d.getTime());
		pstmt.setTime(4,t);
		Timestamp ts = new Timestamp(d.getTime());
		pstmt.setTimestamp(5, ts);
		byte b = 1;
		pstmt.setByte(6, b);
		int count = pstmt.executeUpdate();
		System.out.println("count=" + count);
	}
}

　

存储过程测试

package cn.byref.demo.jdbc;

import java.beans.PropertyVetoException;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;

public class TestProc {
    public static void main(String[] args) {
        try {
            //1.获取Connection对象
            Connection conn = DataSourceManager.getConnection();
            
            //2.创建CallableStatment对象
            CallableStatement cstmt = conn.prepareCall("{call getTest(?,?,?)}");
            
            //3.1 设置输入参数
            cstmt.setInt(1, 1);
            cstmt.setInt(2, 2);
            
            //3.2 设置输出参数
            cstmt.registerOutParameter(3, java.sql.Types.INTEGER);
            
            //4 执行存储过程 获取结果集
            ResultSet rs = cstmt.executeQuery();
            
            //5.打印输出参数
            System.out.println("recordcount:" + cstmt.getInt(3));
            
            //6.循环输出结果集
            while(rs.next()){
                java.util.Date d = new java.util.Date(rs.getTimestamp(6).getTime());
                SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                
                java.util.Date d2 = new java.util.Date(rs.getTime(5).getTime());
                SimpleDateFormat sdf2 = new SimpleDateFormat("HH:mm:ss");
                
                System.out.println(rs.getString(1) + " - " + rs.getString(2) 
                + "-" + rs.getInt(3) + " " + rs.getDate(4) + " begintime:" + sdf2.format(d2)
                + " " + sdf.format(d) + "	" + rs.getBoolean(7));
            }
            rs.close();
            conn.close();
        } catch (PropertyVetoException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }
}

TestModel.java

package cn.byref.demo.jdbc;

import java.util.Date;

public class TestModel {
    private int id;
    private int age;

    public int getAge() {
        return age;
    }

    public void setAge(int age) {
        this.age = age;
    }

    private String username;
    private Date birthday;
    private Date begintime;
    private Date begindatetime;
    private boolean issupper;

    public int getId() {
        return id;
    }
    
    public TestModel(){}

    public TestModel(int id, int age, String username, Date birthday, Date begintime, Date begindatetime,
            boolean issupper) {
        super();
        this.id = id;
        this.age = age;
        this.username = username;
        this.birthday = birthday;
        this.begintime = begintime;
        this.begindatetime = begindatetime;
        this.issupper = issupper;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getUsername() {
        return username;
    }

    public void setUsername(String username) {
        this.username = username;
    }

    public Date getBirthday() {
        return birthday;
    }

    public void setBirthday(Date birthday) {
        this.birthday = birthday;
    }

    public Date getBegintime() {
        return begintime;
    }

    public void setBegintime(Date begintime) {
        this.begintime = begintime;
    }

    public Date getBegindatetime() {
        return begindatetime;
    }

    public void setBegindatetime(Date begindatetime) {
        this.begindatetime = begindatetime;
    }

    public boolean isIssupper() {
        return issupper;
    }

    public void setIssupper(boolean issupper) {
        this.issupper = issupper;
    }
}

事务测试

package cn.byref.demo.jdbc;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Time;
import java.sql.Timestamp;
import java.util.Calendar;

public class TransactionTest {

    public static void main(String[] args) {
        java.util.Date d = Calendar.getInstance().getTime();
        TestModel model = new TestModel(0, 10, "张三1", d, d, d, true);
        TestModel model2 = new TestModel(0, 20, "侠客1", d, d, d, false);

        TransactionTest tt = null;
        try {
            tt = new TransactionTest(DataSourceManager.getConnection());
            tt.beginTrans();
//            tt.setConn();
            System.out.println("model:" + tt.insert(model));

            int x = 1 / 0;

            System.out.println("model2:" + tt.insert(model2));
            tt.commit();

        } 
        catch(Exception e){
            try {
                tt.rollback();
            } catch (SQLException e1) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
            e.printStackTrace();
        }
        finally {
            try {
                tt.close();
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }

    }
    
    public TransactionTest(Connection conn){
        this.conn = conn;
    }

    private Connection conn;

    public void setConn(Connection conn) {
        this.conn = conn;
    }

    public void beginTrans() throws SQLException {
        if (this.conn != null)
            conn.setAutoCommit(false);
    }

    public void commit() throws SQLException {
        if (this.conn != null)
            conn.commit();
    }
    
    public void rollback() throws SQLException{
        if(this.conn != null)
            this.conn.rollback();
    }

    public void close() throws SQLException {
        if (this.conn != null)
            this.conn.close();
    }

    String sql = "insert into test (username,age,birthday,begintime,begindatetime,issupper) values(?,?,?,?,?,?)";

    public int insert(TestModel model) throws SQLException {
        if (model == null) {
            throw new IllegalArgumentException("model is null");
        }
        PreparedStatement pstmt = conn.prepareStatement(sql);
        pstmt.setString(1, model.getUsername());
        pstmt.setInt(2, model.getAge());
        pstmt.setDate(3, new Date(model.getBirthday().getTime()));
        pstmt.setTime(4, new Time(model.getBegintime().getTime()));
        pstmt.setTimestamp(5, new Timestamp(model.getBegindatetime().getTime()));
        pstmt.setBoolean(6, model.isIssupper());
        return pstmt.executeUpdate();
    }
}