package com.aquafold.tools.db;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

public class MSSQLPreparedStatement {
	public static void main(String[] args) {
		try {
			Class.forName("net.sourceforge.jtds.jdbc.Driver");
		}
		catch(ClassNotFoundException e) {
			e.printStackTrace();
		}

		Properties props = new Properties();
		props.put("user", "sa");
		props.put("password", "<password>"); // <-- update password here
		//String host = "<SQL Server 2000>"; // <-- update host here
		String host = "<SQL Server 2008>"; // <-- update host here
		String port = "1433";
		String dbName = "myDB"; // <-- update dbName here
		String url = "jdbc:jtds:sqlserver://" + host + ":" + port + "/" + dbName;
		Connection conn = null;
		PreparedStatement prepStmt = null;
		Statement stmt = null;
		ResultSet rs = null;

		try {
			conn = java.sql.DriverManager.getConnection(url, props);
		} catch(SQLException e) {
			e.printStackTrace();
		}

		/*
		CREATE TABLE [dbo].[import]  ( 
			[c1]	varchar(25) NULL,
			[c2]	varchar(25) NULL 
			)
		*/

		try {
			prepStmt = conn.prepareStatement("INSERT INTO import(c1, c2) values (?, ?)");
			for (int i = 1; i <= 10; i++) {
				prepStmt.setString(1, "a" + i);
				prepStmt.setString(2, "b" + i);
				prepStmt.addBatch();
			}
			prepStmt.executeBatch();
		}
		catch (Exception e) {
			System.out.println("INSERT error : " + e.getMessage());
		}
		finally {
			try {
				if (prepStmt != null) {
					prepStmt.close();
				}
			}
			catch (Exception e) {
			}
			System.out.flush();
		}

		try {
			stmt = conn.createStatement();
			rs = stmt.executeQuery("SELECT c1, c2 FROM import");

			while (rs.next()) {
				System.out.println("c1=" + rs.getString(1) + "  c2=" + rs.getString(2));
			}
		}
		catch (Exception e) {
			System.out.println("SELECT error : " + e.getMessage());
		}
		finally {
			try {
				if (rs != null) {
					rs.close();
				}
				if (stmt != null) {
					stmt.close();
				}
			}
			catch (Exception e) {
			}
			System.out.flush();
		}
	}
}