Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- CREATE TABLE public.states (
- state_id INTEGER DEFAULT nextval('states_seq'::regclass) NOT NULL,
- state_cd VARCHAR(2) NOT NULL,
- name VARCHAR(100) NOT NULL,
- tax_pct NUMERIC(10,2) DEFAULT 0.00 NOT NULL,
- active CHAR(1) DEFAULT 'Y'::bpchar NOT NULL,
- )
- public class State implements Serializable {
- private int stateId;
- private String stateCode;
- private String name;
- private BigDecimal taxPct = new BigDecimal(0);
- private Date expiryDate;
- private String createdBy;
- private Date createdOn;
- private String active;
- //getters and setters here
- }
- public class Main {
- public static void main(String[] args) {
- String url = "jdbc:postgresql://gsi-547576.gsiccorp.net:5432/istore-db";
- String driver = "org.postgresql.Driver";
- String user = "postgres";
- String pwd = "postgres";
- Connection conn = null;
- List<State> states = null;
- try {
- DbUtils.loadDriver(driver);
- conn = DriverManager.getConnection(url, user, pwd);
- states = (List<State>) new QueryRunner().query(conn, "select * from states a where a.active='Y'", new BeanListHandler(State.class);
- System.out.println("states:: " + states);
- } catch (SQLException ex) {
- ex.printStackTrace();
- } finally {
- DbUtils.closeQuietly(conn);
- }
- }
- }
- public class StateBeanProcessor extends BeanProcessor {
- @Override
- protected int[] mapColumnsToProperties(ResultSetMetaData rsmd, PropertyDescriptor[] props) throws SQLException {
- int[] mapping = super.mapColumnsToProperties(rsmd, props);
- /*Map database columns to fields in the order in which they appear
- 1st column in the DB will be mapped to 1st field in the Java
- class and so on.. */
- for(int i=0;i<mapping.length;++i) {
- mapping[i]=i;
- }
- }
- }
- states = (List<State>) new QueryRunner().query(conn, "select * from states", new BeanListHandler(State.class,new BasicRowProcessor(new StateBeanProcessor())));
- public static Map<String,String> mapColumnsToProperties = new HashMap<>();
- static {
- mapColumnsToProperties.put("measure_id","id");
- mapColumnsToProperties.put( "place_number","placeNum");
- mapColumnsToProperties.put( "measure_name","measureName");
- mapColumnsToProperties.put("item_list","itemList");
- mapColumnsToProperties.put("start_time","startTime");
- mapColumnsToProperties.put("end_time","endTime");
- mapColumnsToProperties.put("measure_time","measureTime");
- }
- public static BeanProcessor beanProcessor = new BeanProcessor(mapColumnsToProperties);
- public static RowProcessor rowProcessor = new BasicRowProcessor( beanProcessor);
- ResultSetHandler<List<SafeScheme>> resultSetHandler = new BeanListHandler<SafeScheme>(SafeScheme.class,rowProcessor);
- safeSchemes = queryRunner.query(findListSQL, resultSetHandler);
- public String changeColumnName(String columnName){
- if(columnName == null){
- return null;
- }
- if(columnName.contains("_")){
- char[] cs = columnName.toCharArray();
- int flag = -1;
- for(int index=0;index<columnName.toCharArray().length;index++){
- if(cs[index] == '_'){
- flag = index;
- break;
- }
- }
- columnName = columnName.substring(0, flag) + columnName.substring(flag+1,flag+2).toUpperCase() + columnName.substring(flag+2);
- return changeColumnName(columnName);
- }else{
- return columnName;
- }
- }
- protected int[] mapColumnsToProperties(ResultSetMetaData rsmd,
- PropertyDescriptor[] props) throws SQLException {
- int cols = rsmd.getColumnCount();
- int[] columnToProperty = new int[cols + 1];
- Arrays.fill(columnToProperty, PROPERTY_NOT_FOUND);
- for (int col = 1; col <= cols; col++) {
- String columnName = rsmd.getColumnLabel(col);
- if (null == columnName || 0 == columnName.length()) {
- columnName = rsmd.getColumnName(col);
- }
- String propertyName = columnToPropertyOverrides.get(columnName);
- if (propertyName == null) {
- propertyName = changeColumnName(columnName);//add here
- }
- for (int i = 0; i < props.length; i++) {
- if (propertyName.equalsIgnoreCase(props[i].getName())) {
- columnToProperty[col] = i;
- break;
- }
- }
- }
- return columnToProperty;
- }
- // TODO initialize
- QueryRunner queryRunner = null;
- ResultSetHandler<List<State>> resultSetHandler =
- new BeanListHandler<State>(State.class, new BasicRowProcessor(new GenerousBeanProcessor()));
- // best practice is mentioning only required columns in the query
- final List<State> states = queryRunner.query("select * from states a where a.active='Y'", resultSetHandler);
- for (State state : states) {
- System.out.println(state.getStateId());
- }
Add Comment
Please, Sign In to add comment