SelimSql

Database Management System

Main Page User Guide Download Java Code Android Contact

Select Records

Summary Code Sample

Class.forName("selimsql.jdbc.DbDriver");
String url = "jdbc:selimsql:file:TestSale";
String user = "admin";
String pass = null; //No password.
Connection connection = DriverManager.getConnection(url, user, pass);

Statement statement = connection.createStatement();

String sql = "select * from Product where Id > 0";
ResultSet resultSet = statement.executeQuery(sql);
int row = 0;
while(resultSet.next())
{
	row++;
	int recId = resultSet.getInt("Id");
	System.out.print(row + ". ProductId:" + recId);

	System.out.print(", Name:" + resultSet.getString("Name"));
	System.out.print(", Price:" + resultSet.getBigDecimal("Price"));
	System.out.print(", ProductDate:" + resultSet.getDate("ProductDate"));
	System.out.print(", Status:" + resultSet.getString("Status"));
	System.out.print(", Comment:" + resultSet.getString("Comment"));

	InputStream inputStreamPhoto = resultSet.getBinaryStream("Photo");
	System.out.println(", Photo:" + inputStreamPhoto);

	String fileFullName = "./MyPhoto" + recId + ".png";
	boolean status = writeFileBinary(fileFullName, inputStreamPhoto);
	if (status)
		System.out.println("Write file:" + fileFullName + " okay");

	System.out.println();
}

statement.close();

connection.close();

Detail Steps

1. SelectRecSSqlDb.java

package selimsql.test;

import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class SelectRecSSqlDb {

  private static final String SELIMSQL_JDBC_CLASS      = "selimsql.jdbc.DbDriver";
  private static final String SELIMSQL_JDBC_URL_HEADER = "jdbc:selimsql:";
  private static final String SELIMSQL_DB_TYPE_FILE    = "file";
  private static final String SELIMSQL_DB_TEST_NAME    = "TestDb";

  private static final String TESTDB_USER_ADMIN        = "admin";
  private static final String TESTDB_PASS_NONE         = null;

  public Connection connectDb(String dbName) {
    System.out.println("-SelimSql Connection Testing ------");
    try {
      Class.forName(SELIMSQL_JDBC_CLASS);
    }
    catch(ClassNotFoundException e) {
      System.out.println("No driver for SelimSql?");
      return null;
    }
    System.out.println("SelimSql JDBC Driver Registered!");

    Connection con = null;
    try {
      String url = SELIMSQL_JDBC_URL_HEADER + SELIMSQL_DB_TYPE_FILE + ":" + dbName;
      con = DriverManager.getConnection(url, TESTDB_USER_ADMIN, TESTDB_PASS_NONE);
    }
    catch(SQLException e) {
      System.out.println("Error while connetion:" + e.getMessage());
    }

    if (con!=null)
      System.out.println("Connection succesfull.");
    else
      System.out.println("Connection failed!");

    return con;
  }

  public boolean closeDb(Connection connection) {
    System.out.println("-SelimSql Closing ------");
    if (connection==null) {
      System.out.println("No connection for SelimSql");
      return false;
    }

    try {
      if (connection.isClosed()) {
        System.out.println("Connection is already closed!");
        return false;
      }

      connection.close();
      System.out.println("Connection closed.");
      return true;
    }
    catch(SQLException e) {
      return false;
    }
  }

  private boolean writeFileBinary(String fileFullName, InputStream inputStream)
  {
    if (fileFullName==null || inputStream==null)
      return false;

    FileOutputStream fileOutputStream = null;
    try
    {
      fileOutputStream = new FileOutputStream(fileFullName);

      int lenRead;
      byte[] byteBuf = new byte[1000];
      while((lenRead = inputStream.read(byteBuf)) != -1) {
        fileOutputStream.write(byteBuf, 0, lenRead);
      }

      return true;
    }
    catch(Exception ex) {
      System.out.println("WriteFileError:" + ex.getMessage());
      return false;
    }
    finally
    {
      try {
        if (inputStream!=null) inputStream.close();
        if (fileOutputStream!=null) fileOutputStream.close();
      }
      catch(IOException ex) {
        System.out.println("Error:" + ex.getMessage());
      }
    }
  }


  public boolean selectProductRecords(Connection connection) throws SQLException {
    System.out.println();
    System.out.println("-SelimSql selectOrderRecords operations ------");
    Statement statement = null;
    try {
      statement = connection.createStatement();
      System.out.println("connection.statement okay.");

      String sql = "select * from Product where Id > 0";
      ResultSet resultSet = statement.executeQuery(sql);
      int row = 0;
      while(resultSet.next())
      {
        row++;
        int recId = resultSet.getInt("Id");
        System.out.print(row + ". ProductId:" + recId);
        System.out.print(", Name:" + resultSet.getString("Name"));
        System.out.print(", Price:" + resultSet.getBigDecimal("Price"));
        System.out.print(", ProductDate:" + resultSet.getDate("ProductDate"));
        System.out.print(", Status:" + resultSet.getString("Status"));
        System.out.print(", Comment:" + resultSet.getString("Comment"));

        InputStream inputStreamPhoto = resultSet.getBinaryStream("Photo");
        System.out.println(", Photo:" + inputStreamPhoto);

        String fileFullName = "./MyPhoto" + recId + ".png";
        boolean status = writeFileBinary(fileFullName, inputStreamPhoto);
        if (status)
          System.out.println("Write file:" + fileFullName + " okay");

        System.out.println();
      }
    }
    finally {
      if (statement!=null) {
        statement.close();
        System.out.println("connection.statement closed.");
      }
    }
    return (statement!=null);
  }

  public boolean selectOrderRecords(Connection connection) throws SQLException {
    System.out.println();
    System.out.println("-SelimSql selectOrderRecords operations ------");
    Statement statement = null;
    try {
      statement = connection.createStatement();
      System.out.println("connection.statement okay.");

      String sql = "select * from Order where OrderAmount>=3 and Status='A'";
      ResultSet resultSet = statement.executeQuery(sql);
      int row = 0;
      while(resultSet.next())
      {
        row++;
        System.out.print(row + ". OrderId:" + resultSet.getInt(1));
        System.out.print(", CustomerId:" + resultSet.getInt(2));
        System.out.print(", ProductId:" + resultSet.getInt(3));
        System.out.print(", OrderDate:" + resultSet.getDate(4));
        System.out.print(", OrderAmount:" + resultSet.getShort(5));
        System.out.print(", UnitPrice:" + resultSet.getDouble(6));
        System.out.println(", PaymentType:" + resultSet.getString(7));
        System.out.println();
      }
    }
    finally {
      if (statement!=null) {
        statement.close();
        System.out.println("connection.statement closed.");
      }
    }
    return (statement!=null);
  }

  public static void main(String[] args) {
    SelectRecSSqlDb jdbcTest = new SelectRecSSqlDb();
    Connection dbConnection = null;
    try {
      dbConnection = jdbcTest.connectDb(SELIMSQL_DB_TEST_NAME);
      jdbcTest.selectProductRecords(dbConnection);
      jdbcTest.selectOrderRecords(dbConnection);
    }
    catch(SQLException e) {
      System.out.println("Error:" + e.getMessage());
    }
    finally {
      jdbcTest.closeDb(dbConnection);
    }
  }
}


2. Compile SelectRecSSqlDb.java

md bin
javac src/selimsql/test/SelectRecSSqlDb.java -d bin

3. Download SelimSql JDBC Driver

Download SelimSqlDriver.jar and put it into "lib" directory.


4. Run SelectRecSSqlDb

java -cp "bin;lib/selimSql-1.2.7.jar" selimsql.test.SelectRecSSqlDb
C:\JdbcTest>java -cp "bin;lib/selimSql-1.2.7.jar" selimsql.test.SelectRecSSqlDb
-SelimSql Connection Testing ------
SelimSql JDBC Driver Registered!
Connection succesfull.

-SelimSql selectOrderRecords operations ------
connection.statement okay.
1. ProductId:1, Name:Product1, Price:12.35, ProductDate:2014-11-20, Status:A,
Comment:Product1 Comment Description Long., Photo:null


2. ProductId:2, Name:Product2, Price:23.68, ProductDate:2014-11-19, Status:P,
Comment:Product2 Comment Description Long., Photo:SDbInStream:14493 bytes
Write file:./MyPhoto2.png okay


3. ProductId:3, Name:Product3, Price:80.79, ProductDate:2014-11-18, Status:A,
Comment:Product3 Comment Description Long., Photo:null

4. ProductId:11, Name:Product11, Price:22.35, ProductDate:2014-11-20, Status:A,
Comment:null, Photo:null

5. ProductId:12, Name:Product12, Price:32.68, ProductDate:2014-11-20, Status:P,
Comment:null, Photo:null

6. ProductId:13, Name:Product13, Price:97.00, ProductDate:2014-11-20, Status:A,
Comment:Update Comment Long Description.., Photo:SDbInStream:14493 bytes
Write file:./MyPhoto13.png okay

connection.statement closed.

-SelimSql selectOrderRecords operations ------
connection.statement okay.
1. OrderId:102, CustomerId:11, ProductId:2, OrderDate:2014-11-17, OrderAmount:3,
UnitPrice:5.0, PaymentType:CREDIT

2. OrderId:105, CustomerId:12, ProductId:2, OrderDate:2014-11-13, OrderAmount:5,
UnitPrice:5.0, PaymentType:CASH

3. OrderId:106, CustomerId:13, ProductId:3, OrderDate:2014-11-14, OrderAmount:6,
UnitPrice:7.65, PaymentType:CREDIT

connection.statement closed.
-SelimSql Closing ------
Connection closed.

Download Source Code

Download source code: SSqlDbSample.zip


Proposal

Download the tool SSqlDeveloper to query pratically.

Copyright © 2009 - 2020 SelimSql