Good day! I try to localize all the functions associated with working with the database in the same class. For example, I try to write a function that, at the input, receives a string with the query text, and returns the result.

public Map ReadToMySQL(String query) { try { // opening database connection to MySQL server con = DriverManager.getConnection(url, user, password); // getting Statement object to execute query stmt = con.createStatement(); // executing SELECT query rs = stmt.executeQuery(query); while (rs.next()) { int count = rs.getInt(1); .... } } catch (SQLException sqlEx) { sqlEx.printStackTrace(); } finally { //close connection ,stmt and resultset here try { con.close(); } catch(SQLException se) { /*can't do anything */ } try { stmt.close(); } catch(SQLException se) { /*can't do anything */ } try { rs.close(); } catch(SQLException se) { /*can't do anything */ } } return ....; }

The question is, what should such a function return? It is assumed that the query to the database can be anything: return a different number of variables and variables of different types. I really do not want to write for each request my own function of reading from the database. Maybe there is some standard class architecture for working with the database?

  • one
    And why don't you want to write for each request your own function of reading from the database? That's what they do after all - carapuz
  • The technology for converting data from a relational database is called Object-Relational Mapping . The topic is too extensive to give a definite answer. The decision is highly dependent on your specific situation. - default locale

5 answers 5

I offer 2 ready options:

1) if we want to get the value of specific columns

2) we all want to get

 public class DataBase { public String user = "root"; public String password = "root"; public String connectionString = "localhost:3306"; private Connection connection; private final String DB_DRIVER = "com.mysql.jdbc.Driver"; private int dbConnectionTimeOut = 10000; public DataBase() { } public DataBase(String dbUser, String dbPassword, String connectionHost, int dbConnectionTimeOut) { user = dbUser; password = dbPassword; connectionString = connectionHost; this.dbConnectionTimeOut = dbConnectionTimeOut; } public String[] getArrayFromDB(String table, String[] args) { return getArrayFromDB(table, args, ""); } /** * класс возвращает нам массив из наших столбцов, который мы указали * @param table name of table * @param args columns * @param where * @return */ public String[] getArrayFromDB(String table, String[] args, String where) { String[] result = new String[args.length]; String query = "SELECT "; for (String arg : args) query += arg + ", "; query = query.substring(0, query.length() - 2) + " FROM " + table; if (!where.equals("")) query = query + " WHERE " + where; query = query + ";"; ResultSet resultSet = getResultSet(query); try { if (resultSet.next()) { for (int i = 0; i < args.length; i++) result[i] = resultSet.getString(args[i]); } else for (int i = 0; i < args.length; i++) result[i] = "null"; } catch (SQLException ex) { ex.printStackTrace(); } return result; } // возвращает все данные в виде ArrayList<Map<String,Object>> public ArrayList getEntities(String table){ String baseQuery = "SELECT * FROM " + table; ArrayList entities = new ArrayList<>(); try { entities = (ArrayList) loadObjectFromResultSet(getResultSet(baseQuery)); } catch (Exception e) { e.printStackTrace(); } return entities; } public Object loadObjectFromResultSet(ResultSet resultSet) throws Exception { ArrayList<Object> objectArrayList = new ArrayList<>(); ResultSetMetaData metaData = resultSet.getMetaData(); int columnCount = metaData.getColumnCount(); while(resultSet.next()) { Map<String, Object> map = new HashMap<>(); for (int i = 1; i < columnCount -1 ; i++) { String columnName = metaData.getColumnName(i); Object objectValue = resultSet.getObject(i); map.put(columnName, objectValue); } objectArrayList.add(map); // после того, как всю строку считали, сохраняем в массив и заново } return objectArrayList; } public void connect() { try { // Create MySQL Connection Class.forName(DB_DRIVER); // https://helpx.adobe.com/coldfusion/kb/mysql-error-java-sql-sqlexception.html 0000-00-00 date exception setConnection(DriverManager.getConnection("jdbc:mysql://" + connectionString + "?zeroDateTimeBehavior=convertToNull", user, password)); } catch (Exception ex) { ex.printStackTrace(); } } public ResultSet getResultSet(String sqlQuery) { ResultSet resultSet = null; try { Statement statement = getConnection().createStatement(); resultSet = statement.executeQuery(sqlQuery); } catch (SQLException ex) { ex.printStackTrace(); } return resultSet; } public Connection getConnection() { return connection; } public void setConnection(Connection connection) { this.connection = connection; }

result

  DataBase db = new DataBase(); db.connect(); ArrayList entities = db.getEntities("schema.address"); System.out.println(entities.size());

enter image description here

    If you want to receive an answer to a request in the form of a List<Map<String, String>> , and only send a request as a string to an input, then you obviously have a wrongly designed application architecture.

    Input parameters should be the parameters of the request, not the request itself. And each method should fulfill only its purpose, and not the entire logic of working with the base. Thus, you separate the application logic from the database logic. The result of the method execution must be either a primitive or your object (s).

    If you were told to do this, for example, a special task in the university, then this is another matter. And if you yourself decided so, then you should not do so, as you do not need.

    Do it right ...

     class MyObject { private int id; private String name; public int getId() { return id; } public void setId(int id) { this.id = id; } public String getName() { return name; } public void setName(String name) { this.name = name; } } public DBClassName(String url, String user, String password) { // Init driver here } public void connect() { // Connect to DB } public List<MyObject> getAllData() throws SQLException { try(PreparedStatement ps = conn.prepareStatement("SELECT * FROM table")) { final List<MyObject> result = new ArrayList<>(); final ResultSet rs = ps.executeQuery(); while(rs.next()) { final MyObject obj = new MyObject(); obj.setId(rs.getInt("id")); obj.setName(rs.getString("name")); result.add(obj); } return result; } } public MyObject getDataByID(int id) throws SQLException { try(PreparedStatement ps = conn.prepareStatement("SELECT * FROM table WHERE id=?")) { ps.setInt(1, id); final ResultSet rs = ps.executeQuery(); if(rs.next()) { final MyObject obj = new MyObject(); obj.setId(rs.getInt("id")); obj.setName(rs.getString("name")); return obj; } else { // not found logic here } } }

    PS and never do what the Senior Pomidor user showed in the example. In his example, your application will consume MUCH memory, as no PreparedStatement is closed. No generic parameterized.

    • 1) we get an error if id is BigInteger. 2) the class MyObject is suitable only for the table of the form id, Name. in question it says. what different tables can be - Senior Pomidor
    • Each table and incoming parameters should have its own method. This is if you write correctly. - smie

    In the general case, an associative array, or an array of associative arrays, may be suitable if it is planned to make a selection from several tables in one call. If the exact structure of the database is known, the key Map will be a string, and its value will be Object.

      If there are no restrictions on the use of third-party libraries, I recommend using the Spring Framework. There are much more convenient mechanisms for working with the database than those that you use. There is a good idea how this is done.

        Yesterday I made from the lesson the implementation of input through the console in the MySQL database and search by user name and output the entire string. I will not write the code. There the truth is made in the form of a void-method, but I think you will understand.

        Here is the link.