//
you're reading...
JDBC

JDBC Code with Oracle – Insert

import java.sql.*;

//Using Statement to insert data
public class InsertTest01 {

	public static void main(String[] args) {
		if (args.length != 8) {
	                 throw new IllegalArgumentException("Invalided Parameter:" +
		    "java InsertTest01 job");
		}
	
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
		try {
			//First, load the database driver
			//new OracleDriver();
			Class.forName("oracle.jdbc.driver.OracleDriver");

			//Second, get the database connection
			String dbUrl = "jdbc:oracle:thin:@192.168.1.10:1521:TESTDB";
			String username = "tester";
			String password = "tester";
			conn = DriverManager.getConnection(dbUrl, username, password);

			//get the input parameters
			int empno = Integer.parseInt(args[0]);
			String ename = args[1];
			String job = args[2];
			int mgr = Integer.parseInt(args[3]);
			//yyyy-MM-dd
			String hiredate = args[4];
			float sal = Float.parseFloat(args[5]);
			float comm = Float.parseFloat(args[6]);
			int deptno = Integer.parseInt(args[7]);

			//Third, get the Statement, execute the sql
			//String fight
			//Preferably transferred through more complex sql, sql statements and
                        //procedures in the combination of the FBI
			String sql = "insert into emp(empno, ename, job, mgr, hiredate, sal, comm, deptno) ";
			sql+="values(" + empno + ", '" + ename + "', '" + job + "', " + mgr + ", ";
			sql+="to_date('" + hiredate + "','yyyy-mm-dd'), " + sal + ", " + comm + ", " + deptno + ")";
			System.out.println("sql=" + sql);
			stmt = conn.createStatement();

			stmt.executeUpdate(sql);
			System.out.println("Completed successfully"  + ename + "?");
		}catch(ClassNotFoundException e) {
			e.printStackTrace();
		}catch(SQLException e) {
			e.printStackTrace();
		}finally {

			try {
				if (rs != null) {
					rs.close();
				}
				if (stmt != null) {
					stmt.close();
				}
				if (conn != null) {
					conn.close();
				}
			}catch(SQLException e){}
		}
	}
}

 

import java.sql.*;
import java.text.*;
import java.util.*;
 
//PreparedStatement
public class InsertTest02 {
 
	public static void main(String[] args) {
		if (args.length != 8) {
			throw new IllegalArgumentException("Invalided Parameter:" +
		"java InsertTest02 job");
		}
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			//First, load the database driver
			//new OracleDriver();
			Class.forName("oracle.jdbc.driver.OracleDriver");
 
			//Second, get the database connection
			String dbUrl = "jdbc:oracle:thin:@192.168.1.10:1521:TESTDB";
			String username = "tester";
			String password = "tester";
			conn = DriverManager.getConnection(dbUrl, username, password);
 
			//get the input parameters
			int empno = Integer.parseInt(args[0]);
			String ename = args[1];
			String job = args[2];
			int mgr = Integer.parseInt(args[3]);
			//yyyy-MM-dd
			String hiredate = args[4];
			float sal = Float.parseFloat(args[5]);
			float comm = Float.parseFloat(args[6]);
			int deptno = Integer.parseInt(args[7]);
 
			//Third, get the Statement, execute the sql
			//Preferably transferred through more complex sql, sql statements and
                        //procedures in the combination of the FBI
			String sql = "insert into emp(empno, ename, job, mgr, hiredate, sal, comm, deptno) ";
			sql+="values(?, ?, ?, ?, ?, ?, ?, ?)";
			System.out.println("sql=" + sql);
 
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, empno);
			pstmt.setString(2, ename);
			pstmt.setString(3, job);
			pstmt.setInt(4, mgr);
			
			java.util.Date d = new SimpleDateFormat("yyyy-MM-dd").parse(hiredate);
			pstmt.setDate(5, new java.sql.Date(d.getTime()));
			pstmt.setFloat(6, sal);
			pstmt.setFloat(7, comm);
			pstmt.setInt(8, deptno);
			pstmt.executeUpdate();
 
			System.out.println("The employee name "  + ename + "?");
		}catch(ClassNotFoundException e) {
			e.printStackTrace();
		}catch(SQLException e) {
			e.printStackTrace();
		}catch(ParseException e){
			e.printStackTrace();
		}finally {

			try {
				if (rs != null) {
					rs.close();
				}
				if (pstmt != null) {
					pstmt.close();
				}
				if (conn != null) {
					conn.close();
				}
			}catch(SQLException e){}
		}
	}
}
Advertisements

About daviewning

I am an Oracle DBA

Discussion

No comments yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: