package com.ricky.test;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;

public class StatementTest {
  Connection conn = null;
  Statement stmt = null;
  PreparedStatement pstmt = null;

  public static void main(String[] args) throws SQLException {
    StatementTest SQLExample = new StatementTest();
    SQLExample.doStatement();
    SQLExample.doPreparedStatement();

    SQLExample.conn.close();

  }

  public StatementTest() {
    String sourceURI = "jdbc:odbc:technical_library";
    String driverClass = "sun.jdbc.odbc.JdbcOdbcDriver";

    System.setProperty("jdbc.drivers", driverClass);
    try {
      conn = DriverManager.getConnection(sourceURI);
    } catch (SQLException e) {
      e.printStackTrace();
    }
  }

  public void doStatement() throws SQLException {

    stmt = conn.createStatement();

    String sql = "SELECT * FROM authors";

    ResultSet rs = null;

    rs = stmt.executeQuery(sql);

    showResultSet(rs);

    stmt.close();

    // rs.close();

  }

  public void doPreparedStatement() throws SQLException {
    String sql = "SELECT authid,lastname,fristname FROM authors ORDER BY authid";

    pstmt = conn.prepareStatement(sql);

    ResultSet rs = null;

    rs = pstmt.executeQuery();

    showResultSet(rs);

    pstmt.close();

    // rs.close();
  }

  public void showResultSet(ResultSet rs) throws SQLException {
    ResultSetMetaData metadata = null;

    metadata = rs.getMetaData();

    int columns = 0;

    columns = metadata.getColumnCount();

    System.out
        .println("-------------------------Execute Result-------------------------");

    while (rs.next()) {
      System.out.printf("%-5d", rs.getInt(1));
      for (int i = 2; i < columns; i++) {
        System.out.print(rs.getString(i) + " ");
      }
      System.out.println();
    }

    System.out
        .println("----------------------------Query Metadata----------------------------");

    System.out.println("ResultSet contains " + columns + " columns");

    for (int i = 1; i < columns; i++) {
      System.out.printf(
          "%-3d" + " : " + metadata.getColumnName(i) + "\n", i);
    }

    rs.close();

  }
}