public DataBaseManagement(String driver, String dbURL) {
try {
this.dbURL = dbURL;
Class.forName(driver).newInstance();
conn = DriverManager.getConnection(dbURL);
} catch (InstantiationException
| IllegalAccessException
| ClassNotFoundException
| SQLException e) {
// TODO Auto-generated catch block
if (e instanceof SQLException) {
SQLException sqle = (SQLException) e;
if (sqle.getSQLState().equals("XJ040")) {
sqle = sqle.getNextException();
if (sqle.getSQLState().equals("XSDB6")) {
logger.error(sqle.getMessage() + " Please shutdown that other instance of Derby.");
}
}
} else {
CustomException.errorPrint(e);
}
}
}
public static void main(String args[]) {
try {
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver").newInstance();
String url = "jdbc:Access:///c:/a/db.mdb";
conn = DriverManager.getConnection(url, "", "");
} catch (Exception e) {
e.printStackTrace();
}
}
/** @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response) */
protected void doGet(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
// TODO Auto-generated method stub
response.setContentType("application/json");
JSONArray jsonArray = new JSONArray();
Connection c = null;
Statement stmt = null;
try {
File path = new File(getServletContext().getRealPath("db.sqlite"));
Class.forName("org.sqlite.JDBC");
c = DriverManager.getConnection("jdbc:sqlite:" + path);
c.setAutoCommit(false);
stmt = c.createStatement();
ResultSet rs = stmt.executeQuery("SELECT * FROM smartcard_users;");
while (rs.next()) {
int total_rows = rs.getMetaData().getColumnCount();
JSONObject obj = new JSONObject();
for (int i = 0; i < total_rows; i++) {
String columnName = rs.getMetaData().getColumnLabel(i + 1).toLowerCase();
Object columnValue = rs.getObject(i + 1);
// if value in DB is null, then we set it to default value
if (columnValue == null) {
columnValue = "null";
}
/*
* Next if block is a hack. In case when in db we have
* values like price and price1 there's a bug in jdbc - both
* this names are getting stored as price in ResulSet.
* Therefore when we store second column value, we overwrite
* original value of price. To avoid that, i simply add 1 to
* be consistent with DB.
*/
obj.put(columnName, columnValue);
}
jsonArray.add(obj);
}
rs.close();
stmt.close();
c.close();
} catch (Exception e) {
System.err.println(e.getClass().getName() + ": " + e.getMessage());
System.exit(0);
}
PrintWriter out = response.getWriter();
out.print(jsonArray);
out.close();
}
public static void main(String args[]) {
Connection c = null;
Statement stmt = null;
try {
String decodedPath =
URLDecoder.decode(
javaconnect.class.getProtectionDomain().getCodeSource().getLocation().getPath(),
"UTF-8");
String purl = decodedPath.substring(0, decodedPath.lastIndexOf("/"));
Class.forName("org.sqlite.JDBC");
String name = "atp_01";
// String url = "jdbc:sqlite:" + name;
String url = "jdbc:sqlite:" + purl + "/database/" + name + ".db";
c = DriverManager.getConnection(url);
System.out.println("Opened database successfully");
System.out.println(url);
// main table
stmt = c.createStatement();
String login_atp =
"CREATE TABLE IF NOT EXISTS login_atp ("
+ "idlogin_atp INT NOT NULL, "
+ "first_name_logi VARCHAR(45) NULL, "
+ "last_name_logi VARCHAR(45) NULL,"
+ "username_logi VARCHAR(45) NOT NULL,"
+ "password_logi VARCHAR(45) NOT NULL,"
+ "address_logi VARCHAR(45) NULL,"
+ "email_logi VARCHAR(45) NULL,"
+ "phone_logi VARCHAR(45) NULL,"
+ "PRIMARY KEY (idlogin_atp))";
stmt.executeUpdate(login_atp);
String sql =
"INSERT INTO login_atp (idlogin_atp,first_name_logi,last_name_logi,"
+ "username_logi,password_logi) VALUES (1, 'Trinh', 'Mau', 'dmautrinh',"
+ " '123')";
stmt.executeUpdate(sql);
stmt.close();
c.close();
} catch (Exception e) {
System.err.println(e.getClass().getName() + ": " + e.getMessage());
System.exit(0);
}
System.out.println("Table created successfully");
}
/**
* populate round and sequence with the latest round and generated sequence based on the number of
* trucks
*
* @param number of trucks
* @throws SQLException
*/
private static void initiate(int size) {
// TODO Auto-generated method stub
Statement st = null;
ResultSet rs1 = null;
ResultSet rs2 = null;
ResultSet rs3 = null;
try {
Class.forName("org.postgresql.Driver");
st = conn.createStatement();
// Sql to retrieve last roundnum last:
String sql1 = "SELECT MAX(round_number) FROM rounds WHERE year = " + year;
rs1 = st.executeQuery(sql1);
rs1.next();
int last = rs1.getInt(1);
round = last + 1;
// Create new round
String sql2 = "INSERT INTO rounds (year, round_number) values (" + year + ", " + round + ")";
st.executeUpdate(sql2);
// Create new sequence based on food truck
for (int i = 1; i < size + 1; i++) {
String temp = getSeqQuery(i, round);
st.executeUpdate(temp);
}
// insert into Lottery
// Get truck name and email for lottery insert
Map<Integer, String[]> truckinfo = new HashMap<Integer, String[]>();
String sql4 = "SELECT owner_name, email, id FROM foodtruck";
rs2 = st.executeQuery(sql4);
while (rs2.next()) {
String name = rs2.getString(1).trim();
String email = rs2.getString(2).trim();
int id = rs2.getInt(3);
String[] temp = {name, email};
truckinfo.put(id, temp);
}
// insert into lottery
insertLottery(truckinfo);
} catch (SQLException e) {
e.printStackTrace();
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
/** @param args */
public static void main(String[] args) {
Connection db = null;
try {
Class.forName("org.gjt.mm.mysql.Driver").newInstance();
} catch (Exception e) {
System.out.println("Driver not loaded!");
}
try {
db =
java.sql.DriverManager.getConnection(
"jdbc:mysql://mysql.stud.ntnu.no/bjorfoss_test", "bjorfoss_test", "klovn665");
} catch (SQLException e) {
// TODO Auto-generated catch block
System.out.println("Can't connect to database!");
e.printStackTrace();
}
try {
Statement st = db.createStatement();
ResultSet rs;
String[] columns = {"id", "fastmontert"};
String[] types = {"int NOT NULL AUTO_INCREMENT", "varchar(20) NOT NULL"};
st.execute(createTable("inventar", columns, types));
// rs = st.executeQuery("SELECT * FROM kunder");
// while(rs.next()){
// String fornavn = rs.getString("fornavn");
// String etternavn = rs.getString("etternavn");
// System.out.println(fornavn + " " + etternavn);
// }
// db.close();
rs = st.executeQuery("SELECT * FROM inventar");
while (rs.next()) {
String fornavn = rs.getString("id");
String etternavn = rs.getString("fastmontert");
System.out.println(fornavn + " " + etternavn);
}
db.close();
} catch (Exception e) {
System.out.println("Shit happened!");
e.printStackTrace();
}
}
public DBConnection(String user, String pass, Component fThis) {
{
try {
url = new StringBuilder();
url.append("jdbc:postgresql://");
url.append(resources.getString("server"));
url.append(":");
url.append("5432");
url.append("/");
url.append(resources.getString("database"));
System.out.println(url);
Class.forName("org.postgresql.Driver");
con = DriverManager.getConnection(url.toString(), user, pass);
bError = true;
} catch (java.sql.SQLException se) {
JOptionPane jfo = new JOptionPane(se.getMessage(), JOptionPane.INFORMATION_MESSAGE);
JDialog dialog = jfo.createDialog(fThis, "Message");
dialog.setModal(true);
dialog.setVisible(true);
bError = false;
// System.exit(1);
} catch (ClassNotFoundException ce) {
JOptionPane jfo = new JOptionPane(ce.getMessage(), JOptionPane.INFORMATION_MESSAGE);
JDialog dialog = jfo.createDialog(fThis, "Message");
dialog.setModal(true);
dialog.setVisible(true);
bError = false;
// System.exit(1);
} finally {
// try {
// in.close();
// } catch (IOException ex) {
// Logger.getLogger(DBConnection.class.getName()).log(Level.SEVERE, null,
// ex);
// }
}
}
}
// Возвращает соединение с БД, которое будем использовать в последующих методах
private static Connection getDBConnection() throws Exception {
String JDBC_DRIVER = "org.gjt.mm.mysql.Driver";
String DB_URL = "jdbc:mysql://localhost/revisor";
// Способ здания параметров подключения к БД через класс Properties
// Это нужно, что бы задать кодировку подключения, а не только логин и пароль
Properties properties = new Properties();
properties.setProperty("user", "root");
properties.setProperty("password", "root");
/*
Настройки указывающие о необходимости конвертировать данные из Unicode
в UTF-8, который используется в нашей таблице для хранения данных
*/
properties.setProperty("useUnicode", "true");
properties.setProperty("characterEncoding", "UTF-8");
Class.forName(JDBC_DRIVER);
return DriverManager.getConnection(DB_URL, properties);
// Обычный, простой формат Коннекшена
// return = DriverManager.getConnection(DB_URL, "root", "root");
}
private static void insertLottery(Map<Integer, String[]> truckinfo) {
try {
Class.forName("org.postgresql.Driver");
Statement st = conn.createStatement();
// randomly generate order
Collections.shuffle(data);
int position = 0;
while (position < data.size()) {
int temp = data.get(position);
String s = getLotteryQuery(temp, truckinfo, position + 1);
st.executeUpdate(s);
position++;
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
