Blog Archives
migration C#, Java, C++ (day 6), MySQL
It is nearly midnight now. 7 minutes are left. Phew, I made it. Good night.
Discipline or stupidity? I guess both 😉
Was fun though. I am achieving my targets.
But now I need something soft, no more hardcore today. Maybe a cushion … Zzzzzzz
MySQL
private void ExecuteNonQuery(MySqlConnection xConnection, string xCommand) {
MySqlCommand lCommand = xConnection.CreateCommand();
lCommand.CommandText = xCommand;
lCommand.ExecuteNonQuery();
} //
private void ExecuteNonQueryAddRow(MySqlConnection xConnection, string xName, DateTime xBirthday) {
MySqlCommand lCommand = xConnection.CreateCommand();
lCommand.CommandText = "INSERT INTO test (name, birthdate) values (@name, @birthdate)";
MySqlParameterCollection lParams = lCommand.Parameters;
lParams.AddWithValue("@name", xName);
lParams.AddWithValue("@birthdate", xBirthday);
lCommand.ExecuteNonQuery();
} //
private DataTable ExecuteQuery(MySqlConnection xConnection, string xCommand) {
MySqlCommand lCommand = xConnection.CreateCommand();
lCommand.CommandText = xCommand;
MySqlDataAdapter lAdapter = new MySqlDataAdapter(lCommand);
DataSet lDataSet = new DataSet();
lAdapter.Fill(lDataSet);
return lDataSet.Tables[0];
} //
public void MySqlConnectionTest() {
string lConnectionString = "Server=127.0.0.1;Database=ohta;Uid=root;Pwd=root;";
try {
using (MySqlConnection lConnection = new MySqlConnection(lConnectionString)) {
lConnection.Open();
//DataTable lSchema = lConnection.GetSchema(); // not required to get or set anything, see lConnectionString
// create a new table
string lField1 = "id INT NOT NULL AUTO_INCREMENT,";
string lField2 = "name VARCHAR(40) NOT NULL,";
string lField3 = "birthdate DATE,";
string lKey = "PRIMARY KEY (id)";
string lTableDefinition = lField1 + lField2 + lField3 + lKey;
ExecuteNonQuery(lConnection, "DROP TABLE IF EXISTS test");
ExecuteNonQuery(lConnection, "CREATE TABLE test(" + lTableDefinition + ");");
// fill the table with values
ExecuteNonQueryAddRow(lConnection, "George Washington", new DateTime(1732, 2, 22));
ExecuteNonQueryAddRow(lConnection, "Abraham Lincoln", new DateTime(1809, 2, 12));
ExecuteNonQueryAddRow(lConnection, "Ronald Reagan", new DateTime(1911, 2, 6));
ExecuteNonQueryAddRow(lConnection, "Bill Clinton", new DateTime(1946, 8, 19));
// Query
DataTable lTable = ExecuteQuery(lConnection, "SELECT * FROM test");
// print column names
foreach (DataColumn lColumn in lTable.Columns) Console.Write(lColumn.ColumnName + "; ");
Console.WriteLine();
// print rows
int lColumnCount = lTable.Columns.Count;
foreach (DataRow lRow in lTable.Rows) {
for (int i = 0; i < lColumnCount; i++) {
object o = lRow[i];
if (o is DateTime) {
DateTime lDateTime = (DateTime)o;
Console.Write(lDateTime.ToString("dd MMM yyyy") + "; ");
continue;
}
Console.Write(o.ToString() + "; ");
}
Console.WriteLine();
}
// clean up
ExecuteNonQuery(lConnection, "DROP TABLE IF EXISTS test");
}
}
catch (Exception ex) {
Console.WriteLine(ex.Message + Environment.NewLine + ex.StackTrace);
}
} //
package Demos;
import java.sql.*;
import java.util.*;
public class MySqlDemo {
public static Calendar getDate(int xYear, int xMonth, int xDay, int xHour, int xMinute, int xSecond) {
Calendar lCalendar = Calendar.getInstance();
lCalendar.clear();
lCalendar.set(xYear, xMonth - 1, xDay, xHour, xMinute, xSecond);
return lCalendar;
} //
private int ExecuteNonQuery(Connection xConnection, String xCommand) {
Statement lStatement = null;
try {
lStatement = xConnection.createStatement();
return lStatement.executeUpdate(xCommand);
} catch (SQLException ex) {
System.out.println("SqlException: " + ex.getMessage());
} finally {
CloseStatement(lStatement);
}
return -1;
} //
private void ExecuteNonQueryAddRow(Connection xConnection, String xName, Calendar xBirthday) {
// old school
//String lBirthday = String.format("%1$tY-%1$tm-%1$te", xBirthday);
//String lCommand = String.format("INSERT INTO test (name, birthdate) values ('%s', '%s')", xName, lBirthday);
//int lResult = ExecuteNonQuery(xConnection, lCommand);
// modern school
PreparedStatement lStatement = null;
try {
java.sql.Date lBirthday = new java.sql.Date(xBirthday.getTimeInMillis());
lStatement = xConnection.prepareStatement("INSERT INTO test (name, birthdate) VALUES (?,?)");
lStatement.setString(1, xName);
lStatement.setDate(2, lBirthday, xBirthday);
lStatement.executeUpdate();
} catch (SQLException ex) {
System.out.println("SqlException: " + ex.getMessage());
} finally {
CloseStatement(lStatement);
}
} //
private void CloseStatement(Statement xStatement) {
if (xStatement == null) return;
try {
xStatement.close();
} catch (SQLException ex) {
System.out.println("SqlException: " + ex.getMessage());
}
} //
private void CloseStatement(PreparedStatement xStatement) {
if (xStatement == null) return;
try {
xStatement.close();
} catch (SQLException ex) {
System.out.println("SqlException: " + ex.getMessage());
}
} //
private void CloseResultSet(ResultSet xResultSet) {
if (xResultSet == null) return;
try {
xResultSet.close();
} catch (SQLException ex) {
System.out.println("SqlException: " + ex.getMessage());
}
} //
private void ExecuteQuery(Connection xConnection, String xCommand) {
ResultSet lResultSet = null;
Statement lStatement = null;
try {
lStatement = xConnection.createStatement();
lResultSet = lStatement.executeQuery(xCommand);
// print column headers
ResultSetMetaData lMetaData = lResultSet.getMetaData();
for (int i = 1, n = lMetaData.getColumnCount(); i <= n; i++)
System.out.print(lMetaData.getColumnLabel(i) + " ");
System.out.println();
// print data
while (lResultSet.next()) {
long lId = lResultSet.getLong(1);
String lName = lResultSet.getString(2);
java.sql.Date lBirthday = lResultSet.getDate(3);
System.out.println(lId + ": " + lName + " was born on " + lBirthday.toString());
}
} catch (SQLException ex) {
System.out.println("SqlException: " + ex.getMessage());
} finally {
CloseStatement(lStatement);
CloseResultSet(lResultSet);
}
} //
public final void MySqlConnectionTest() {
String lIpAddress = "127.0.0.1:3306";
String lDatabase = "ohta";
String lUser = "root";
String lPassword = "root";
Connection lConnection = null;
try {
Class.forName("com.mysql.jdbc.Driver");
System.out.println("driver connection successful");
String lUrl = "jdbc:mysql://" + lIpAddress + "/" + lDatabase;
lConnection = DriverManager.getConnection(lUrl, lUser, lPassword);
System.out.println("database connection successful");
// create a new table
String lField1 = "id INT NOT NULL AUTO_INCREMENT,";
String lField2 = "name VARCHAR(40) NOT NULL,";
String lField3 = "birthdate DATE,";
String lKey = "PRIMARY KEY (id)";
String lTableDefinition = lField1 + lField2 + lField3 + lKey;
ExecuteNonQuery(lConnection, "DROP TABLE IF EXISTS test");
ExecuteNonQuery(lConnection, "CREATE TABLE test(" + lTableDefinition + ");");
// fill the table with values
ExecuteNonQueryAddRow(lConnection, "George Washington", getDate(1732, 2, 22, 0, 0, 0));
ExecuteNonQueryAddRow(lConnection, "Abraham Lincoln", getDate(1809, 2, 12, 0, 0, 0));
ExecuteNonQueryAddRow(lConnection, "Ronald Reagan", getDate(1911, 2, 6, 0, 0, 0));
ExecuteNonQueryAddRow(lConnection, "Bill Clinton", getDate(1946, 8, 19, 0, 0, 0));
// Query
ExecuteQuery(lConnection, "SELECT * FROM test");
// clean up
ExecuteNonQuery(lConnection, "DROP TABLE IF EXISTS test");
} catch (ClassNotFoundException ex) {
System.out.println("ClassNotFoundException: " + ex.getMessage());
} catch (SQLException ex) {
System.out.println("SqlException: " + ex.getMessage());
} finally {
if (lConnection != null) try {
lConnection.close();
} catch (SQLException ex) {
System.out.println("SqlException: " + ex.getMessage());
}
}
} //
public static void main(String[] args) {
MySqlDemo lDemo = new MySqlDemo();
lDemo.MySqlConnectionTest();
} //
} // class
// .h file
#pragma once
#include <stdlib.h>
#include <iostream>
#include "mysql_connection.h"
#include <cppconn/driver.h>
#include <cppconn/exception.h>
#include <cppconn/resultset.h>
#include <cppconn/statement.h>
#include <cppconn/prepared_statement.h>
using namespace std;
using namespace sql;
class Day6 {
private:
void ExecuteNonQuery(Statement * const xStatement, const string &xCommand);
void ExecuteNonQueryAddRow(PreparedStatement *xPreparedStatement, const string &xName, const int &xYear, const int &xMonth, const int &xDay);
ResultSet *ExecuteQuery(Connection *xConnection, const string &xCommand);
public:
int test(void);
};
// .cpp file
#include "stdafx.h"
#include "Day6.h"
void Day6::ExecuteNonQuery(Statement * const xStatement, const string &xCommand) {
SQLString lCommand(xCommand);
xStatement->execute(lCommand);
} //
void Day6::ExecuteNonQueryAddRow(PreparedStatement *xPreparedStatement, const string &xName, const int &xYear, const int &xMonth, const int &xDay) {
SQLString lName(xName);
xPreparedStatement->setString(1, lName);
int64_t lBirthday = xYear * 10000 + xMonth * 100 + xDay;
xPreparedStatement->setInt64(2, lBirthday);
xPreparedStatement->executeUpdate();
} //
ResultSet *Day6::ExecuteQuery(Connection *xConnection, const string &xCommand) {
SQLString lCommand(xCommand);
PreparedStatement *lPreparedStatement = xConnection->prepareStatement(lCommand);
ResultSet *lResultSet = lPreparedStatement->executeQuery();
delete lPreparedStatement;
return lResultSet;
} //
int Day6::test(void) {
try {
// connect
Driver *lDriver = get_driver_instance();
Connection *lConnection = lDriver->connect("tcp://127.0.0.1:3306", "root", "root");
// lConnection->setAutoCommit(0); // turn off auto commit
lConnection->setSchema("ohta"); // replace it with your database name
// create a new table
string lField1 = "id INT NOT NULL AUTO_INCREMENT,";
string lField2 = "name VARCHAR(40) NOT NULL,";
string lField3 = "birthday INT,"; // YYYYMMDD
string lKey = "PRIMARY KEY (id)";
string lTableDefinition = lField1 + lField2 + lField3 + lKey;
string lCommand = "CREATE TABLE test(" + lTableDefinition; lCommand += ")";
Statement *lStatement = lConnection->createStatement();
ExecuteNonQuery(lStatement, "DROP TABLE IF EXISTS test");
ExecuteNonQuery(lStatement, lCommand);
delete lStatement;
// fill the table with values
PreparedStatement *lPreparedStatement = lConnection->prepareStatement("INSERT INTO test (name, birthday) values (?, ?)");
ExecuteNonQueryAddRow(lPreparedStatement, "George Washington", 1732, 2, 22);
ExecuteNonQueryAddRow(lPreparedStatement, "Abraham Lincoln", 1809, 2, 12);
ExecuteNonQueryAddRow(lPreparedStatement, "Ronald Reagan", 1911, 2, 6);
ExecuteNonQueryAddRow(lPreparedStatement, "Bill Clinton", 1946, 8, 19);
delete lPreparedStatement;
// Query
ResultSet *lResultSet = ExecuteQuery(lConnection, "SELECT * FROM test;");
// print column names
ResultSetMetaData *lMeta = lResultSet->getMetaData();
unsigned int lColumnCount = lMeta->getColumnCount();
for (unsigned int i = 1; i <= lColumnCount; i++) {
SQLString lColumnName = lMeta->getColumnName(i); // http://bugs.mysql.com/bug.php?id=70006
SQLString lTypeName = lMeta->getColumnTypeName(i);
cout << lColumnName.asStdString() << ": " << lTypeName.asStdString() << endl;
}
// print rows
lResultSet->beforeFirst();
while (lResultSet->next()) {
string lId = to_string(lResultSet->getInt("id"));
SQLString h = lResultSet->getString("name"); // http://bugs.mysql.com/bug.php?id=70006
const string lName = h.asStdString();
int64_t lBirthday = lResultSet->getInt("birthday");
int lYear = (int)( lBirthday / 10000);
int lMonth = (int)(lBirthday % 10000) / 100;
int lDay = (int)(lBirthday % 100);
cout << lId << ", " << lName << ", " << lYear << "/" << lMonth << "/" << lDay << endl;
}
// clean up
lStatement = lConnection->createStatement();
ExecuteNonQuery(lStatement, "DROP TABLE IF EXISTS test");
delete lStatement;
delete lResultSet;
lConnection->close();
delete lConnection;
}
catch (SQLException &e) {
cout << "exception" << e.getSQLState() << endl;
}
return EXIT_SUCCESS;
}
C# Hardcore Programming 