[Training Video - 7] [Database connection] Part 1

import groovy.sql.Sql

// obtain the connection to database

// do the transaction

// close database connection

try{
// connecting to db
def dbURL="jdbc:mysql://localhost:3306/retail"
def dbUsername="root"
def dbPassword=""
def dbDriver="com.mysql.jdbc.Driver"
def db = Sql.newInstance(dbURL,dbUsername,dbPassword,dbDriver)

// interact with DB

/**********************Select query*******************************/
def q1 = "select * from product" // simple select query -  more than 1 row
def q2 = "select * from product where prod_id='4'" // 1 row
def q3 = "select * from product where prod_name like '%QTP%'" // more than 1 

// eachRow, rows

db.eachRow(q3){
//log.info "${it.prod_name}" +" --  " + "${it.prod_price}" 	
log.info it[0] + "  " + it[1] + "  " + it[2]
}

// count of the rows which i get
// add variables in the query
def x ='Nike'
def q4 = "select * from product where prod_name=$x"
db.eachRow(q4){
//log.info "${it.prod_name}" +" --  " + "${it.prod_price}" 	
log.info it[0] + "  " + it[1] + "  " + it[2]
}
log.info "*******Multiple parameters**********"
def name='Catch 22'
def category_id='6'
def pro_id='12'
def q5 = "select * from product where prod_name=$name and cat_id=$category_id and prod_id=$pro_id"
db.eachRow(q5){
//log.info "${it.prod_name}" +" --  " + "${it.prod_price}" 	
log.info it[0] + "  " + it[1] + "  " + it[2]
}

log.info "Using list in the query"
def params=['Catch 22','6','12']
def q6 = "select * from product where prod_name=? and cat_id=? and prod_id=?"
db.eachRow(q6,params){
	log.info "$it.prod_name"
}


log.info "****************ROWS Function***********************"

def result = db.rows(q1)
log.info "Total number of rows in the result " + result.size()
log.info result.get(0).get("prod_id")+"  "+result.get(0).get("prod_name")
log.info result.get(5).get("prod_id")+"  "+result.get(5).get("prod_name")
// complete output
for(i=0;i<result.size();i++){
	log.info result.get(i).get("prod_id")+"  "+result.get(i).get("prod_name")
}
// adding parameters
log.info "Adding parameters in the query with variable"
result = db.rows(q4)
log.info "Total number of rows " + result.size()
log.info result.get(0).get("prod_id") + "   " + result.get(0).get("prod_name")

// Map containing the parameters
log.info "*******MAP********"
def myMap =[x:'Harry Potter',y:'11']
def query="select * from product where prod_id=:y and prod_name=:x"
result = db.rows(query,myMap)
log.info "Total rows " + result.size
log.info result.get(0).get("prod_id") + "   " + result.get(0).get("cat_id")+"  "+result.get(0).get("prod_name")

// List containing the parameters
log.info "********LIST************"
def p1=['Catch 22','6','12']
def q7 = "select * from product where prod_name=? and cat_id=? and prod_id=?"
result = db.rows(q7,p1)
log.info "Total rows " + result.size
log.info result.get(0).get("prod_id") + "   " + result.get(0).get("cat_id")+"  "+result.get(0).get("prod_name")

// firing a query
}catch(Exception e){
log.info "Some db error"
log.info e.getMessage()

}finally{

// close database connection	
db.close()
}