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

Try, catch and finally in DB connection

Forming Groovy Connection string and obtaining connection Object

Firing Select Query and obtaining results

Foreach and rows functions

Finding number of rows in result

Import groovy.sql.sql//Obtain the connection to database//do the transaction//close database connectiontry{//connectin G to Dbdef 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 rowdef q2 = "SELECT * from Product where prod_id= ' 4 '"//1 Rowdef q3 = "SELECT * from product where prod_name like '%qt p% '//More than 1//Eachrow, Rowsdb.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 querydef x = ' Nike ' def Q4 = "Selec T * 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" def category_id= ' 6 ' def pro_id= ' def q5 = "SELECT * FROM Product wher E 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 ', ' 1 2 ']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 outputfor (I=0;i<result.size (); i++) {Log.info result.get (i). Get ("prod_id") + "" +result.get (i). Get ("Prod_ Name ")}//adding Parameterslog.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 Parameterslog.info" *******map******** "def myMap =[x: ' Harry Potter ', y: ' One ']def query= ' SELECT * from product where prod_id=:y and prod_name=:x "result = Db.rows (query,mymap) log.info" Total rows "+ Result.sizel Og.info result.get (0). Get ("prod_id") + "" "+ result.get (0). Get (" cat_id ") +" "+result.get (0). Get (" Prod_name ")//List cont Aining the Parameterslog.info "********list************" def p1=[' Catch ', ' 6 ', ']def q7 = ' SELECT * FROM product where Prod_name=? and cat_id=? and prod_id=? " result = Db.rows (Q7,P1) log.info "Total rows" + Result.sizelog.info result.get (0). Get ("prod_id") + "" + result.get (0). g ET ("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 Connectiondb.close ()}

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