jdbc

目录结构

E:\workspace\testjdbc>tree /F
Folder PATH listing
Volume serial number is 9C15-503D
E:.
│  build.xml
│  sql.properties
│
├─classes
│  └─com
│      └─fangjian
│              TestJDBC1.class
│
├─lib
│      commons-dbcp-1.4.jar
│      commons-pool-1.6.jar
│      mysql-connector-java-5.1.24-bin.jar
│
└─src
    └─com
        └─fangjian
                TestJDBC1.java

build.xml:

<project default="run">
	<path id="classpath">
		<fileset dir="lib" includes="**/*.jar"></fileset>
	</path>
	<target name="build" >
		<javac srcdir="src" destdir="classes" classpathref="classpath" includeantruntime="false"  debug="true"></javac>
	</target>
	<target name="run" depends="build">
		<java classname="com.fangjian.TestJDBC1">
			<classpath>
				<path location="classes"></path>
				<path refid="classpath"></path>
			</classpath>
		</java>
	</target>
</project>

sql.properties

connection.driver=com.mysql.jdbc.Driver
connection.url=jdbc:mysql://localhost:3306/test?user=fangjian

TestJDBC1.java

package com.fangjian;
import java.sql.*;
import com.mysql.jdbc.Driver;
import java.util.*;
import java.io.*;
import org.apache.commons.dbcp.BasicDataSource;
import javax.sql.DataSource;

public class TestJDBC1{
	public static void main(String[] args)throws Exception{
		System.out.println("hello");
		loadProp();
		dataSource=setupDataSource(props.getProperty("connection.url"));
		readTable();
		System.out.println("insert:-----------------");
		insertTable();
		readTable();
		System.out.println("update:-----------------");
		updateTable();
		readTable();
		System.out.println("delete:-----------------");
		deleteTable();
		readTable();
	}

	private static Properties props=new Properties();
	private static DataSource dataSource = null;


	public static DataSource setupDataSource(String connectURI) {
        BasicDataSource ds = new BasicDataSource();
        ds.setDriverClassName("com.mysql.jdbc.Driver");
        ds.setUrl(connectURI);
        return ds;
    }

	public static void loadProp() throws Exception{
		InputStream in=new FileInputStream("sql.properties");
		props.load(in);
		in.close();
	}

	public static void readTable() throws Exception{
		Connection conn=null;
		Statement stmt=null;
		ResultSet rs=null;
		try{
			conn=dataSource.getConnection();
			stmt=conn.createStatement();
			String query="select * from abc";
			rs=stmt.executeQuery(query);
			while(rs.next()){
				System.out.println(rs.getString(1));
			}
		}
		catch(Exception e){
			e.printStackTrace();
		}
		finally{
			try{
				if(null!=conn)conn.close();
			}catch(Exception e)
			{}
		}
	}

	public static void insertTable()throws Exception{
		Connection conn=null;
		PreparedStatement state=null;
		try{
			conn=dataSource.getConnection();
			state=conn.prepareStatement("insert into abc(x) values(?)");
			state.setString(1,"newline");
			state.executeUpdate();
		}finally{
			try{
				if(null!=conn)conn.close();
			}catch(Exception e)
			{}
		}
	}

	public static void updateTable()throws Exception{
		Connection conn=null;
		PreparedStatement state=null;
		try{
			conn=dataSource.getConnection();
			state=conn.prepareStatement("update abc set x='updated' where x=?");
			state.setString(1,"newline");
			state.executeUpdate();
		}finally{
			try{
				if(null!=conn)conn.close();
			}catch(Exception e)
			{}
		}
	}
	public static void deleteTable()throws Exception{
		Connection conn=null;
		PreparedStatement state=null;
		try{
			conn=dataSource.getConnection();
			state=conn.prepareStatement("delete from abc where x=?");
			state.setString(1,"updated");
			state.executeUpdate();
		}finally{
			try{
				if(null!=conn)conn.close();
			}catch(Exception e)
			{}
		}
	}


}

table:

mysql> describe abc;
+-------+------------+------+-----+---------+-------+
| Field | Type       | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| x     | varchar(9) | YES  |     | NULL    |       |
+-------+------------+------+-----+---------+-------+
1 row in set (0.02 sec)