Member Avatar for chicago1985

I have a class that fetches a record but dont think I need to use List object because I am fetching one record and not an array of records.

public List getRecords(){
        ResultSet rs = null;
        Statement stmt = null;
        Connection connection = null;
 
        List rows = new ArrayList();
        
        try
        {
            Class.forName("org.gjt.mm.mysql.Driver");
            connection = DriverManager.getConnection("jdbc:mysql://localhost/dbase?user=myname&password=thepassword");
            stmt = connection.createStatement();
            rs = stmt.executeQuery("SELECT * from user where userid = 10");  
            
            while(rs.next()){
            RowBean row = new RowBean();
            row.setFirstname(rs.getString("firstname"));
            row.setLastname( rs.getString("lastname"));
            rows.add(row); 
            .....

Would this be correct to just return a RowBean object and if so am I doing it correctly?

public RowBean getRecord(){ 
        ResultSet rs = null; 
        Statement stmt = null; 
        Connection connection = null; 
 
 
        //List rows = new ArrayList(); 
        RowBean row = new RowBean();
 
        try 
        { 
            Class.forName("org.gjt.mm.mysql.Driver"); 
            connection = DriverManager.getConnection("jdbc:mysql:// 
localhost/dbase?user=myname&password=thepassword"); 
            stmt = connection.createStatement(); 
            rs = stmt.executeQuery("SELECT * from user where userid = 
10"); 
 
 
            while(rs.next()){ 
           
            row.setFirstname(rs.getString("firstname")); 
            row.setLastname( rs.getString("lastname")); 
            //rows.add(row); 
            }
            ..... 
            //last part of method I would return the RowBean object:
            return row;
}
  • 2 Contributors
  • 1 Reply
  • 128 Views
  • 19 Hours Discussion Span
  • Latest Post Latest Post by Ezzaral

Dani AI

Generated

Yes — returning a single RowBean is the right approach when your query must return at most one row. A few practical fixes make the method safer and clearer than the original snippet:

  • Do not create and return a bean before you know a row exists. That returns a "blank" bean when no row was found. Return null or, preferably in modern Java, Optional<RowBean> to make absence explicit.
  • Use a PreparedStatement with a parameter (and LIMIT 1 if appropriate) instead of a plain Statement. This avoids SQL injection and is clearer about intent.
  • Use try-with-resources so the Connection, PreparedStatement and ResultSet are always closed automatically (as pointed out).

Example pattern (Java 8+):

public Optional<RowBean> findById(DataSource ds, int userId) throws SQLException {
    String sql = "SELECT firstname, lastname FROM user WHERE userid = ? LIMIT 1";
    try (Connection conn = ds.getConnection();
         PreparedStatement ps = conn.prepareStatement(sql)) {
        ps.setInt(1, userId);
        try (ResultSet rs = ps.executeQuery()) {
            if (rs.next()) {
                RowBean row = new RowBean();
                row.setFirstname(rs.getString("firstname"));
                row.setLastname(rs.getString("lastname"));
                return Optional.of(row);
            } else {
                return Optional.empty();
            }
        }
    }
}

Additional notes: prefer explicit column names over SELECT *; use a DataSource/connection pool in production rather than DriverManager; decide on exception handling (throw, wrap, or translate to a DAO exception) instead of swallowing SQLException; and consider a small mapping utility or a lightweight library (Spring JdbcTemplate / jOOQ / JPA) if you have many similar methods.

Member Avatar for Ezzaral

Yes, that would be just fine I would imagine. You don't show the rest of the try block, but be sure to close the statement and connection in a finally{} clause so you don't chew up your database resources.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.