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

最新推荐文章于 2025-08-22 11:10:38 发布

转载最新推荐文章于 2025-08-22 11:10:38 发布 · 52 阅读

0 ·

CC 4.0 BY-SA版权

原文链接：http://www.cnblogs.com/MasterMonkInTemple/p/4860950.html

文章标签：

#数据库

本文介绍使用Groovy进行数据库连接、查询及结果处理的方法。包括建立连接、执行查询、获取结果并计数等基本操作，并展示了如何使用eachRow和rows函数处理查询结果。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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 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()
}

转载于:https://www.cnblogs.com/MasterMonkInTemple/p/4860950.html