JDBC之PreparedStatement

package com.saas.jdbc;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.PreparedStatement;
import java.util.Calendar;
import java.sql.ResultSet;
/**
 * 此类主要使用面向对象的思想，
 *  使用PreparedStatment语句执行，删除，修改，查询等操作
 *  此PreparedStatment 语句和Statment语句有什么区别
 *  PreparedStatment是预编译语句，Statment是语句的子类为PreparedStatment,CallableStatment;
 * @author Administrator
 *
 */

public class TestPrepareDemo01 {
	

	public static String _URL="jdbc:mysql://127.0.0.1:3306/shopping_thpad_com?useUnicode=true&characterEncoding=UTF-8&useOldAliasMetadataBehavior=true";
	
	public static String _LOGIN_NAME="root";
	
	public static String _LOGIN_PASSWORD="123456";
	
	private static Connection connect = null;
	
	private PreparedStatement pstmt=null;
	
	private ResultSet rs = null;
	
	public static Connection getConnection() {
		try{
			Class.forName("com.mysql.jdbc.Driver");
		}
		catch(ClassNotFoundException e) {
			e.printStackTrace();
		}
		
		
		try {
			connect = DriverManager.getConnection(_URL, _LOGIN_NAME, _LOGIN_PASSWORD);	
		}
		catch(SQLException e) {
			e.printStackTrace();
		}
		return connect;
		
	}
	
	public void execInsert() {
		connect = getConnection();
		//添加
		String insert_sql ="insert into item_spec(item_spec_id,name,remarks) values(?,?,?)";
		try {
			Calendar cal =	Calendar.getInstance();
		 	pstmt= connect.prepareStatement(insert_sql);
		 	pstmt.setString(1,String.valueOf(cal.getTimeInMillis()));
		 	pstmt.setString(2, "test");
		 	pstmt.setString(3, "test333333333");
		 	pstmt.executeUpdate();
		}
		catch(SQLException e) {
			e.printStackTrace();
		}
		finally {
			// 抛出异常则关闭所有链接
			this.doClose();
		}
	}
	
	public void execStatement() {
		connect = getConnection();
		
		String result_sql = "select * from item_spec ";//where item_spec_id=?
		
		try {
			pstmt = connect.prepareStatement(result_sql);
			//pstmt.setString(1, "1319729859937");
			rs = pstmt.executeQuery();
			
			while(rs.next()) {
				System.out.println(rs.getString("item_spec_id")+"-----------"+rs.getString("name")+"---------------"+rs.getString("remarks"));
			}
		}
		catch(SQLException e) {
			e.printStackTrace();
		}
		finally {
			 this.doClose();
		}
	}
	
	public void execUpdate() {
		connect = getConnection();
		
		String update_sql ="update item_spec set name=?,remarks=? where item_spec_id=?";
		
		try {
			PreparedStatement pstmt = connect.prepareStatement(update_sql);
			pstmt.setString(1, "zhudansheng");
			pstmt.setString(2, "remarks ssstaskdflasdjfjasdfkljasdaskfjaafkjas手放开垃圾生大幅就 ");
			pstmt.setString(3, "1319125327157");
			int i_flag=pstmt.executeUpdate();
			System.out.println("更新为："+i_flag);
		}
		catch(SQLException e) {
			e.printStackTrace();
		}
		finally{
			this.doClose();
		}
	}
	
	public void execDelete() {
		connect = getConnection();
		String delete_sql ="delete from item_spec where item_spec_id=?";
		try {
			PreparedStatement pstmt = connect.prepareStatement(delete_sql); 
			pstmt.setString(1, "1319122161343");
			int i_flag=pstmt.executeUpdate();
			System.out.println("删除为："+i_flag);
		}
		catch(SQLException e) {
			e.printStackTrace();
		}
		finally{
			this.doClose();
		}
	}
	
	public void doClose() {
		try {
			if(null!=rs) {
				rs.close();
				rs=null;
			}
			if(null!=pstmt) {
				pstmt.close();
				pstmt=null;
			}
			if(null!=connect) {
				connect.close();
				connect=null;
			}
				
		}
		catch(SQLException e) {
			e.printStackTrace();
		}
	}
	
	public static void main(String[]args) {
		TestPrepareDemo01 test = new TestPrepareDemo01();
		test.getConnection();
		test.execInsert();
		test.execStatement();
		test.execUpdate();
		test.execDelete();
		test.doClose();
	}
}