All Downloads are FREE. Search and download functionalities are using the official Maven repository.

net.sf.gluebooster.java.booster.basic.db.DbAccess Maven / Gradle / Ivy

Go to download 

package net.sf.gluebooster.java.booster.basic.db;

import java.io.Closeable;
import java.io.IOException;
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.util.ArrayList;
import java.util.HashMap;
import java.util.List;

import net.sf.gluebooster.java.booster.basic.container.ParameterizedStatement;

/**
 * Access information to connect to a database
 * @author cbauer
 *
 * @defaultParamText connectionString used to connect to the database 
 * @defaultParamText classname  a new instance of this class will be created. Probably the driver class.
 * @defaultParamText username  user of the database 
 * @defaultParamText password  password of the user 
 * @defaultParamText initialStatements  initial sql statements 
 * @defaultParamText closeConnectionImmediately  should the connection be closed immediately after the access 
 * @defaultParamText connection  the created connection 
 */
public class DbAccess implements Closeable {

	/**
	 * used to connect to the database 
	 */
	private String connectionString;
	
	/**
	 * a new instance of this class will be created. Probably the driver class.
	 */
	private String classname;
	
	/**
	 * user of the database 
	 */
	private String username;
	
	/**
	 * password
	 */
	private String password;
	
	/**
	 * initial sql statements 
	 */
	private List initialStatements;

	/**
	 * should the connection be closed immediately after the access
	 */
	private boolean closeConnectionImmediately;
	
	/**
	 * the connection to the database
	 */
	private Connection connection;


	/**
	 * Create access to a MySql database.
	 * Use mysql-connector. Example
	 * 
	 * 
	 * 		
	 *		mysql
	 *		mysql-connector-java
	 *		8.0.16
	 *
	 *	
	 * 

	 * 
	 * @param hostname hostname of the database
	 * @param port port of the database
	 * @param dbName name of the database
	 * @param readonly should it be only a readonly access?
	 * @return the created access
	 */
	public static DbAccess createMySql(String hostname, String port, String dbName, String username, String password, boolean readonly) {
		// classname would be "com.mysql.cj.jdbc.Driver" but is no longer necessary
		DbAccess result = new DbAccess("jdbc:mysql://" + hostname + ":" + port + "/" + dbName
				+ "?useUnicode=true&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=UTC", username, password);

		if (readonly) {
			result.initialStatements.add("SET SESSION TRANSACTION READ ONLY");
		}

		return result;
	}

	public DbAccess(String connectionString, String username, String password) {
		this.connectionString = connectionString;
		this.username = username;
		this.password = password;
		this.initialStatements = new ArrayList();
	}

	public DbAccess(String connectionString, String classname, String username, String password) {
		this(connectionString, username, password);
		this.classname = classname;
	}

	/** 
	 * Creates a db connection from the current data.
	 * 
	 * @return the db connection
	 */
	private Connection createConnection() throws Exception {
		if (classname != null) {
			Class.forName(classname).newInstance();
		}
		Connection result = DriverManager.getConnection(connectionString, username, password);
		for (String statement : initialStatements) {
			result.createStatement().execute(statement);
		}

		return result;
	}

	/**
	 * Executes multiple statements
	 * @param statements will be executed
	 * @return a list with the results of the statements
	 */
	public List execute(ParameterizedStatement... statements) throws Exception {
		List result = new ArrayList();
		if (connection == null) {
			connection = createConnection();
		}
		try {
			for (ParameterizedStatement statement : statements) {
				result.add(execute(connection, statement));
			}
		} finally {
			if (closeConnectionImmediately) {
				connection.close();
				connection = null;
			}
		}

		return result;
	}

	/**
	 * Execute one statement
	 * @param statement will be executed
	 * @return several different classes
	 */
	private Object execute(Connection connection, ParameterizedStatement statement) throws Exception {
		PreparedStatement prep = connection.prepareStatement(statement.getStatement());
		int i = 1;
		for (Object param : statement.getParameters()) {
			prep.setObject(i++, param);
		}

		Object type = statement.getType();

		if (Sql.QUERY.equals(type)) {
			ResultSet sqlResult = prep.executeQuery();// sql.executeQuery(statement);
			if (closeConnectionImmediately) {
				// need to parse the resultset
				List result = new ArrayList();
				ResultSetMetaData meta = sqlResult.getMetaData();

				int numberOfColumns = meta.getColumnCount();
				while (sqlResult.next()) {
					HashMap row = new HashMap();
					for (i = 1; i < numberOfColumns; i++) {
						row.put(meta.getColumnLabel(i), sqlResult.getObject(i));
					}
					result.add(row);
				}
				return result;
			} else {
				return new ResultSetIterator(sqlResult);

			}
		} else if (Sql.UPDATE.equals(type)) {
			return prep.executeUpdate();

		} else {
			throw new IllegalStateException("type not supported: " + type);
		}
	}


	public String getConnectionString() {
		return connectionString;
	}

	public void setConnectionString(String connectionString) {
		this.connectionString = connectionString;
	}

	public String getClassname() {
		return classname;
	}

	public void setClassname(String classname) {
		this.classname = classname;
	}

	public String getUsername() {
		return username;
	}

	public void setUsername(String username) {
		this.username = username;
	}

	public String getPassword() {
		return password;
	}

	public void setPassword(String password) {
		this.password = password;
	}

	/** 
	 * Sample
	 * @param ignored is not used
	 */
	public static void main(String[] ignored) throws Exception {
		DbAccess db = DbAccess.createMySql("localhost", "3306", "cb", "root", "admin", true);
		db.setCloseConnectionImmediately(true);
		ParameterizedStatement statement1 = new ParameterizedStatement(Sql.QUERY, "select * from fehlerlog limit 10");
		ParameterizedStatement statement2 = new ParameterizedStatement(Sql.UPDATE,
				"update fehlerlog set nachricht = '" + System.currentTimeMillis() + "' where id = 2");

		List results = db.execute(statement1);
		System.out.println(results);
		results = db.execute(statement1, statement2);
		System.out.println(results);

		// Connection connection = db.createConnection();
		// System.out.println(connection);
		// ResultSetIterator iterator = (ResultSetIterator) statement1.execSql(connection);
		// ResultSet set = iterator.next();
		// Object obj = set.getObject(1);
		// System.out.println(obj);
		// statement2 = new ParameterizedStatement(Sql.UPDATE, "update fehlerlog set nachricht = '" + System.currentTimeMillis() + "' where id = 2");
		// statement2.execSql(connection);
		//
		// connection.close();
	}

	@Override
	public void close() throws IOException {
		if (connection != null) {
			try {
				connection.close();
			} catch (SQLException e) {
				throw new IOException(e);
			}
		}

	}

	public boolean isCloseConnectionImmediately() {
		return closeConnectionImmediately;
	}

	public void setCloseConnectionImmediately(boolean closeConnectionImmediately) {
		this.closeConnectionImmediately = closeConnectionImmediately;
	}

}
    

    

    

            

    

            



    
    






    
© 2015 - 2024 Weber Informatics LLC | Privacy Policy