桂馬の学習ノード: SQLite

2015-03-05

Use SQLite in Java(Netbeans)

It's my study note.
I made it by reference to Jens-André Koch's webpage, and updated it into a netbeans 8.0 with SQLite 3.8 version.

Step 1. Download the SQLite JDBC Library

To use SQLite in Netbeans, we must download the library at first.
We can get the library here:
https://bitbucket.org/xerial/sqlite-jdbc

Step 2. Add the JDBC Driver into your project

Add the downloaded JAR into our project.
It can be copied into the project's lib dir, too.

At this moment, it was sqlite-jdbc-3.8.7.jar

Step 3. use it in your code

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

import org.sqlite.JDBC;

public class JavaSQLiteTest {

    public static void main(String[] args) throws ClassNotFoundException {
        // load the sqlite-JDBC driver using the current class loader
        //Class.forName("org.sqlite.JDBC"); // you can use this if you don't use the import.
        
        Connection connection = null;
        try{
            connection = DriverManager.getConnection("jdbc:sqlite:sample.db");
            Statement stm = connection.createStatement();
            
            stm.setQueryTimeout(30); // set timeout to 30 sec.
            stm.executeUpdate("drop table if exists person");
            stm.executeUpdate("create table person (id integer, name string)");
            stm.executeUpdate("insert into person values(1,'leo')");
            stm.executeUpdate("insert into person values(2,'yui')");
            
            ResultSet rs = stm.executeQuery("select * from person");
            while(rs.next()){
                // read the result set
                System.out.println("name = " + rs.getString("name"));
                System.out.println("id = " +rs.getInt("id"));
            }
        }catch(SQLException e){
            // if the error message is "out of memory",
            // it probably means no database file is found
            System.err.println(e.getMessage());
        }finally{
            try{
                if(connection != null)
                    connection.close();
                
            }catch(SQLException e){
                // connection close failed.
                System.err.println(e);
            }
        }
    }
}

Step 4. to manage your database in netbeans

1. Add the JDBC driver in Service->Databases->Drivers
there should be "org.sqlite.JDBC" showed in "Driver Class", and the Name field is "SQLite".
2. Add a JDBC link by SQLite JDBC Driver
right-click on the SQLite Driver, and select "Connecting Using...". Then give the JDBC URL as you like.
To connect with the file named "sample.db", the JDBC URL should be jdbc:sqlite:sample.db 3. Done.

2013-04-05

Connect to Databases in C#

今回はC#でSQLiteやMySQLに繋ぎ方法を記録する。

一言で言うと、SQL Serverとの繋ぎはほぼ似ている。
ただし使用するメソッド／オブジェクトをSqlXxxからSQLiteXxxあるいはMySqlXxxに変わるだけ。

さて、本題に入る。先ずは基本のSQL serverに行こう。
することは全部同じ。

データベースに繋ぎ（連結する）。
CarというTableを作成。
テーブルCarにデータを入れる。
データを出力し、連結をクローズする。

using System;
using System.Data.SqlClient;

class SQLTest01
{
    static void Main()
    {
        /* Set the connection string
         * With the setting below:
         * user id : the userid for SQL server
         * password or pwd : the password of user
         * database : the database you want to connect
         */
        string cs = "user id=testuser;" +
            "password=testpwd;server=localhost;" +
            "Trusted_Connection=yes;" +
            "database=dbTest; " +
            "connection timeout=30";

        // Connection to Database
        // with the new "using" garbage collection 
        using (SqlConnection con = new SqlConnection(cs))
        {
            // Open connection;
            con.Open();

            // Create the Table
            using (SqlCommand cmd = new SqlCommand())
            {
                cmd.Connection = con;

                cmd.CommandText = "DROP TABLE IF EXISTS Cars";
                cmd.ExecuteNonQuery();
                cmd.CommandText = @"CREATE TABLE Cars(Id INTEGER PRIMARY KEY, 
                    Name TEXT, Price INT)";
                cmd.ExecuteNonQuery();
            }

            // Put Datas into Table
            using(SqlCommand cmd = new SqlCommand())
            {
                cmd.Connection = con;

                // do with Prepare();
                cmd.CommandText = "INSERT INTO Cars(Id, Name, Price) VALUES(@Id, @Name, @Price)";
                cmd.Prepare();
                cmd.Parameters.AddWithValue("@Id", 1);
                cmd.Parameters.AddWithValue("@Name", "Audi");
                cmd.Parameters.AddWithValue("@Price", 52642);
                cmd.ExecuteNonQuery();
                cmd.Parameters.AddWithValue("@Id", 2);
                cmd.Parameters.AddWithValue("@Name", "Mercedes");
                cmd.Parameters.AddWithValue("@Price", 57127);
                cmd.ExecuteNonQuery();

                // do with Standard SQL command
                cmd.CommandText = "INSERT INTO Cars VALUES(3,'Skoda',9000)";
                cmd.ExecuteNonQuery();
                cmd.CommandText = "INSERT INTO Cars VALUES(4,'Volvo',29000)";
                cmd.ExecuteNonQuery();
                cmd.CommandText = "INSERT INTO Cars VALUES(5,'Bentley',350000)";
                cmd.ExecuteNonQuery();
                cmd.CommandText = "INSERT INTO Cars VALUES(6,'Citroen',21000)";
                cmd.ExecuteNonQuery();
                cmd.CommandText = "INSERT INTO Cars VALUES(7,'Hummer',41400)";
                cmd.ExecuteNonQuery();
                cmd.CommandText = "INSERT INTO Cars VALUES(8,'Volkswagen',21600)";
                cmd.ExecuteNonQuery();
            }

            // Read Datas with DataReader
            string stm = "SELECT * FROM Cars LIMIT 5";
            using (SqlCommand cmd = new SqlCommand(stm,con))
            {
                using (SqlDataReader rdr = cmd.ExecuteReader())
                {
                    while (rdr.Read())
                    {
                        Console.WriteLine(rdr.GetInt32(0) + " " + rdr.GetString(1) + " " + rdr.GetInt32(2));
                    }
                }
            }

            // Close connection;
            con.Close();
        }
    }
}

続き、似ている内容で、SQLiteでしましょう。
注意してほしい点は

事前にSystem.Data.SQLiteのDLLファイルをプロジェクトのフォルダ下のExternalsにコピーしておくこと。また、プロジェクト設定でそれに参照するように設定して下さい。
DLLファイルを持っていない場合、SQLite.NET公式サイトあるいはSQLite公式サイトより探して下さい。無償で手に入れるはず。

using System;
using System.Data.SQLite;

class SQLTest01
{
    static void Main()
    {
        /* Set the connection string
         * With the setting below:
         * user id : the userid for SQL server
         * password or pwd : the password of user
         * database : the database you want to connect
         */
        string cs = "URI=file:test.db";

        // Connection to Database
        // with the new "using" garbage collection 
        using (SQLiteConnection con = new SQLiteConnection(cs))
        {
            // Open connection;
            con.Open();

            // Create the Table
            using (SQLiteCommand cmd = new SQLiteCommand())
            {
                cmd.Connection = con;

                cmd.CommandText = "DROP TABLE IF EXISTS Cars";
                cmd.ExecuteNonQuery();
                cmd.CommandText = @"CREATE TABLE Cars(Id INTEGER PRIMARY KEY, 
                    Name TEXT, Price INT)";
                cmd.ExecuteNonQuery();
            }

            // Put Datas into Table
            using (SQLiteCommand cmd = new SQLiteCommand())
            {
                cmd.Connection = con;

                // do with Prepare();
                cmd.CommandText = "INSERT INTO Cars(Id, Name, Price) VALUES(@Id, @Name, @Price)";
                cmd.Prepare();
                cmd.Parameters.AddWithValue("@Id", 1);
                cmd.Parameters.AddWithValue("@Name", "Audi");
                cmd.Parameters.AddWithValue("@Price", 52642);
                cmd.ExecuteNonQuery();
                cmd.Parameters.AddWithValue("@Id", 2);
                cmd.Parameters.AddWithValue("@Name", "Mercedes");
                cmd.Parameters.AddWithValue("@Price", 57127);
                cmd.ExecuteNonQuery();

                // do with Standard SQL command
                cmd.CommandText = "INSERT INTO Cars VALUES(3,'Skoda',9000)";
                cmd.ExecuteNonQuery();
                cmd.CommandText = "INSERT INTO Cars VALUES(4,'Volvo',29000)";
                cmd.ExecuteNonQuery();
                cmd.CommandText = "INSERT INTO Cars VALUES(5,'Bentley',350000)";
                cmd.ExecuteNonQuery();
                cmd.CommandText = "INSERT INTO Cars VALUES(6,'Citroen',21000)";
                cmd.ExecuteNonQuery();
                cmd.CommandText = "INSERT INTO Cars VALUES(7,'Hummer',41400)";
                cmd.ExecuteNonQuery();
                cmd.CommandText = "INSERT INTO Cars VALUES(8,'Volkswagen',21600)";
                cmd.ExecuteNonQuery();
            }

            // Read Datas with DataReader
            string stm = "SELECT * FROM Cars LIMIT 5";
            using (SQLiteCommand cmd = new SQLiteCommand(stm, con))
            {
                using (SQLiteDataReader rdr = cmd.ExecuteReader())
                {
                    while (rdr.Read())
                    {
                        Console.WriteLine(rdr.GetInt32(0) + " " + rdr.GetString(1) + " " + rdr.GetInt32(2));
                    }
                }
            }

            // Close connection;
            con.Close();
        }

        Console.WriteLine("Press ENTER to continue...");
        Console.ReadLine();
    }
}

最後、MySQL（今回はローカルを例にする）でしましょう。
SQLiteの時と同じ、System.Data.MySQLのDLLを取得して下さい。SQLiteの時と同じ、MySQLの公式サイトより無償で手に入れるはず。

using System;
using MySql.Data.MySqlClient;

class SQLTest01
{
    static void Main()
    {
        /* Set the connection string
         * With the setting below:
         * user id : the userid for SQL server
         * password or pwd : the password of user
         * database : the database you want to connect
         */
        string cs = "user id=testuser;" +
            "password=testpwd;server=localhost;" +
            "database=testDB; " +
            "connection timeout=30";

        // Connection to Database
        // with the new "using" garbage collection 
        using (MySqlConnection con = new MySqlConnection(cs))
        {
            // Open connection;
            con.Open();

            // Create the Table
            using (MySqlCommand cmd = new MySqlCommand())
            {
                cmd.Connection = con;

                cmd.CommandText = "DROP TABLE IF EXISTS Cars";
                cmd.ExecuteNonQuery();
                cmd.CommandText = @"CREATE TABLE Cars(Id INTEGER PRIMARY KEY, 
                    Name TEXT, Price INT)";
                cmd.ExecuteNonQuery();
            }

            // Put Datas into Table
            using (MySqlCommand cmd = new MySqlCommand())
            {
                cmd.Connection = con;

                // do with Prepare();
                cmd.CommandText = "INSERT INTO Cars(Id, Name, Price) VALUES(1, 'Audi', 52642)";
                cmd.ExecuteNonQuery();
                cmd.CommandText = "INSERT INTO Cars(Id, Name, Price) VALUES(2, 'Mercedes', 57127)";
                cmd.ExecuteNonQuery();
                cmd.CommandText = "INSERT INTO Cars VALUES(3,'Skoda',9000)";
                cmd.ExecuteNonQuery();
                cmd.CommandText = "INSERT INTO Cars VALUES(4,'Volvo',29000)";
                cmd.ExecuteNonQuery();
                cmd.CommandText = "INSERT INTO Cars VALUES(5,'Bentley',350000)";
                cmd.ExecuteNonQuery();
                cmd.CommandText = "INSERT INTO Cars VALUES(6,'Citroen',21000)";
                cmd.ExecuteNonQuery();
                cmd.CommandText = "INSERT INTO Cars VALUES(7,'Hummer',41400)";
                cmd.ExecuteNonQuery();
                cmd.CommandText = "INSERT INTO Cars VALUES(8,'Volkswagen',21600)";
                cmd.ExecuteNonQuery();
            }

            // Read Datas with DataReader
            string stm = "SELECT * FROM Cars LIMIT 5";
            using (MySqlCommand cmd = new MySqlCommand(stm, con))
            {
                using (MySqlDataReader rdr = cmd.ExecuteReader())
                {
                    while (rdr.Read())
                    {
                        Console.WriteLine(rdr.GetInt32(0) + " " + rdr.GetString(1) + " " + rdr.GetInt32(2));
                    }
                }
            }

            // Close connection;
            con.Close();
        }
    }
}

桂馬の学習ノード

SyntaxHighlighter