package cs3220.service; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import java.util.ArrayList; import java.util.List; import cs3220.model.Character; import cs3220.model.House; public class DbService { private String url = "jdbc:mysql://cs3.calstatela.edu/cs3220stu31"; private String username = "cs3220stu31"; private String password = "abcd1234"; private Connection connection; public DbService() { try { connection = DriverManager.getConnection( url, username, password ); } catch( SQLException e ) { e.printStackTrace(); } } public void close() { if( connection != null ) { try { connection.close(); } catch( SQLException e ) { e.printStackTrace(); } } } public List getHouses() { List houses = new ArrayList(); try { Statement stmt = connection.createStatement(); ResultSet rs = stmt.executeQuery( "select * from houses" ); while( rs.next() ) { House house = new House( rs.getString( "name" ) ); houses.add( house ); } stmt.close(); } catch( SQLException e ) { e.printStackTrace(); } return houses; } public List getCharacters() { List characters = new ArrayList(); try { Statement stmt = connection.createStatement(); ResultSet rs = stmt.executeQuery( "select * from characters" ); while( rs.next() ) { Character c = new Character( rs.getString( "name" ), rs.getString( "mother" ), rs.getString( "father" ) ); characters.add( c ); } stmt.close(); } catch( SQLException e ) { e.printStackTrace(); } return characters; } public List getCharacters( String house ) { List characters = new ArrayList(); try { String sql = "select * from characters where house = ?"; PreparedStatement pstmt = connection.prepareStatement( sql ); pstmt.setString( 1, house ); ResultSet rs = pstmt.executeQuery(); while( rs.next() ) { Character c = new Character( rs.getString( "name" ), rs.getString( "mother" ), rs.getString( "father" ) ); characters.add( c ); } pstmt.close(); } catch( SQLException e ) { e.printStackTrace(); } return characters; } public void addHouse( String name ) { try { String sql = "insert into houses (name) values (?)"; PreparedStatement pstmt = connection.prepareStatement( sql, Statement.RETURN_GENERATED_KEYS ); pstmt.setString( 1, name ); pstmt.executeUpdate(); pstmt.close(); } catch( SQLException e ) { e.printStackTrace(); } } public void addCharacter( String name, String father, String mother, String house ) { try { String sql = "insert into characters values (?, ?, ?, ?)"; PreparedStatement pstmt = connection.prepareStatement( sql, Statement.RETURN_GENERATED_KEYS ); pstmt.setString( 1, name ); pstmt.setString( 2, father ); pstmt.setString( 3, mother ); pstmt.setString( 4, house ); pstmt.executeUpdate(); pstmt.close(); } catch( SQLException e ) { e.printStackTrace(); } } }