4 answers
3 accepted
Thanks for sharing your code.
After reviewing it, below are my suggestions.
Instead of using the ConnectionFactory etc., to establish the DB Connection, you can use the ScriptRunner DatabaseUtil, i.e. com.onresolve.scriptrunner.DB.DatabaseUtil as shown below:
import com.onresolve.scriptrunner.db.DatabaseUtil
def output = []
def result = [:]
DatabaseUtil.withSql('local_db') { sql ->
output = sql.rows("select email from email_list where username = ?", loggedInUser.name)
}
In the sample code above, I am using ScriptRunner's Resources to set up the DB Connection. You can configure it using the Local Database your Jira instance is using, as shown in the print screens below:-
and just set the connection pool name. The connection pool name I am using in the code is local_db, as shown below:-
Alternatively, if you want to connect to an alternative DB, you can use the Database Configuration option as shown below and specify your DB connection details:-
If you are using the rows query, i.e. sql.rows, you will first need to iterate the result and pass the value to a Map as shown below:-
def output = []
def result = [:]
DatabaseUtil.withSql('local_db') { sql ->
output = sql.rows("select email from email_list where username = ?", loggedInUser.name)
}
output.each {
result = it as Map<String,String>
}
If you directly pass the output to the Email object, you are actually passing a List instead of a String. This is why you are encountering the Error.
If you view the com.atlassian.jira.mail.Email class, you will notice that the constructor of the class is taking a single String parameter or multiple String parameters and not a List as shown below:-
Next, you will need to pass the value from the Map to the Email object using the values().findAll option as shown below:-
result.values().findAll { emailAddress ->
def email = new Email(emailAddress.toString())
.
.
.
}
Lastly, when you are triggering the Mail, you will need to use the contextClassLoader from the Thread to be able to deliver the mail as shown below:-
result.values().findAll { emailAddress ->
def email = new Email(emailAddress.toString())
email.setSubject(subject)
email.setBody(body)
email.setMimeType("text/html")
def threadClassLoader = Thread.currentThread().contextClassLoader
Thread.currentThread().contextClassLoader = mailServer.class.classLoader
mailServer.send(email)
Thread.currentThread().contextClassLoader = threadClassLoader
}
Below is a complete working sample code for your reference:-
import com.atlassian.jira.bc.issue.search.SearchService
import com.atlassian.jira.component.ComponentAccessor
import com.atlassian.jira.jql.parser.JqlQueryParser
import com.atlassian.jira.mail.Email
import com.atlassian.jira.web.bean.PagerFilter
import com.onresolve.scriptrunner.db.DatabaseUtil
def jqlQueryParser = ComponentAccessor.getComponent(JqlQueryParser)
def searchService = ComponentAccessor.getComponent(SearchService)
def loggedInUser = ComponentAccessor.jiraAuthenticationContext.loggedInUser
def mailServerManager = ComponentAccessor.mailServerManager
def query = jqlQueryParser.parseQuery("filter=sample_query")
def search = searchService.search(loggedInUser, query, PagerFilter.unlimitedFilter)
def mailServer = mailServerManager.defaultSMTPMailServer
def output = []
def result = [:]
DatabaseUtil.withSql('local_db') { sql ->
output = sql.rows("select email from email_list where username = ?", loggedInUser.name)
}
output.each {
result = it as Map<String,String>
}
if(search.results) {
search.results.each {
def subject = "JIRA Ticket CHECK"
def body = "Test CHECK ${it.key} ${it.summary} "
result.values().findAll { emailAddress ->
def email = new Email(emailAddress.toString())
email.setSubject(subject)
email.setBody(body)
email.setMimeType("text/html")
def threadClassLoader = Thread.currentThread().contextClassLoader
Thread.currentThread().contextClassLoader = mailServer.class.classLoader
mailServer.send(email)
Thread.currentThread().contextClassLoader = threadClassLoader
}
}
}
Please note, this example code is not 100% exact to your environment. Hence, you will need to make the required modifications.
I hope this helps to answer your question. :)
Thank you and Kind regards,
Ram
Thanks for sharing your code.
After reviewing it, below are my suggestions.
Instead of using the ConnectionFactory etc., to establish the DB Connection, you can use the ScriptRunner DatabaseUtil, i.e. com.onresolve.scriptrunner.DB.DatabaseUtil as shown below:
import com.onresolve.scriptrunner.db.DatabaseUtil
def output = []
def result = [:]
DatabaseUtil.withSql('local_db') { sql ->
output = sql.rows("select email from email_list where username = ?", loggedInUser.name)
}
In the sample code above, I am using ScriptRunner's Resources to set up the DB Connection.
You can configure it using the Local Database your Jira instance is using ScriptRunner's Resources, as shown in the print screens below:-
and just set the connection pool name. The connection pool name I am using in the code is local_db, as shown below:-
Alternatively, if you want to connect to an alternative DB, you can use the Database Configuration option as shown below and specify your DB connection details:-
If you are using the rows query, i.e. sql.rows, you will first need to iterate the result and pass the value to a Map as shown below:-
def output = []
def result = [:]
DatabaseUtil.withSql('local_db') { sql ->
output = sql.rows("select email from email_list where username = ?", loggedInUser.name)
}
output.each {
result = it as Map<String,String>
}
If you directly pass the output to the Email object, you are passing a List instead of a String. This is why you are encountering the Error.
If you view the com.atlassian.jira.mail.Email class, you will notice that the constructor of the class is taking a single String parameter or multiple String parameters and not a List as shown below:-
Next, you will need to pass the value from the Map to the Email object using the values().findAll option as shown below:-
result.values().findAll { emailAddress ->
def email = new Email(emailAddress.toString())
.
.
.
}Lastly, when you are triggering the Mail, you will need to use the contextClassLoader from the Thread to be able to deliver the mail as shown below:-
result.values().findAll { emailAddress ->
def email = new Email(emailAddress.toString())
email.setSubject(subject)
email.setBody(body)
email.setMimeType("text/html")
def threadClassLoader = Thread.currentThread().contextClassLoader
Thread.currentThread().contextClassLoader = mailServer.class.classLoader
mailServer.send(email)
Thread.currentThread().contextClassLoader = threadClassLoader
}Below is a complete working sample code for your reference:-
import com.atlassian.jira.bc.issue.search.SearchService
import com.atlassian.jira.component.ComponentAccessor
import com.atlassian.jira.jql.parser.JqlQueryParser
import com.atlassian.jira.mail.Email
import com.atlassian.jira.web.bean.PagerFilter
import com.onresolve.scriptrunner.db.DatabaseUtil
def jqlQueryParser = ComponentAccessor.getComponent(JqlQueryParser)
def searchService = ComponentAccessor.getComponent(SearchService)
def loggedInUser = ComponentAccessor.jiraAuthenticationContext.loggedInUser
def mailServerManager = ComponentAccessor.mailServerManager
def query = jqlQueryParser.parseQuery("filter=sample_query")
def search = searchService.search(loggedInUser, query, PagerFilter.unlimitedFilter)
def mailServer = mailServerManager.defaultSMTPMailServer
def output = []
def result = [:]
DatabaseUtil.withSql('local_db') { sql ->
output = sql.rows("select email from email_list where username = ?", loggedInUser.name)
}
output.each {
result = it as Map<String,String>
}
if(search.results) {
search.results.each {
def subject = "JIRA Ticket CHECK"
def body = "Test CHECK ${it.key} ${it.summary} "
result.values().findAll { emailAddress ->
def email = new Email(emailAddress.toString())
email.setSubject(subject)
email.setBody(body)
email.setMimeType("text/html")
def threadClassLoader = Thread.currentThread().contextClassLoader
Thread.currentThread().contextClassLoader = mailServer.class.classLoader
mailServer.send(email)
Thread.currentThread().contextClassLoader = threadClassLoader
}
}
}
Please note, this example code is not 100% exact to your environment. Hence, you will need to make the required modifications.
I hope this helps to answer your question. :)
Thank you and Kind regards,
Ram
Thanks for sharing your code.
I have reviewed it and below are my suggestions.
If you are using the rows query, i.e. sql.rows, you will first need to iterate the result and pass the value to a Map as shown below:-
def output = []
def result = [:]
DatabaseUtil.withSql('local_db') { sql ->
output = sql.rows("select email from email_list where username = ?", loggedInUser.name)
}
output.each {
result = it as Map<String,String>
}
If you directly pass the output to the Email object, you are actually passing a List instead of a String. This is why you are encountering the Error.
If you view the com.atlassian.jira.mail.Email class, you will notice that the constructor of the class is taking a single String parameter or multiple String parameters and not a List as shown below:-
Next, you will need to pass the value from the Map to the Email object using the values().findAll option as shown below:-
result.values().findAll { emailAddress ->
def email = new Email(emailAddress.toString())
.
.
.
}
Lastly, when you are triggering the Mail, you will need to use the contextClassLoader from the Thread to be able to deliver the mail as shown below:-
result.values().findAll { emailAddress ->
def email = new Email(emailAddress.toString())
email.setSubject(subject)
email.setBody(body)
email.setMimeType("text/html")
def threadClassLoader = Thread.currentThread().contextClassLoader
Thread.currentThread().contextClassLoader = mailServer.class.classLoader
mailServer.send(email)
Thread.currentThread().contextClassLoader = threadClassLoader
}
Below is a sample code for your reference:-
import com.atlassian.jira.bc.issue.search.SearchService
import com.atlassian.jira.component.ComponentAccessor
import com.atlassian.jira.jql.parser.JqlQueryParser
import com.atlassian.jira.mail.Email
import com.atlassian.jira.web.bean.PagerFilter
import com.onresolve.scriptrunner.db.DatabaseUtil
def jqlQueryParser = ComponentAccessor.getComponent(JqlQueryParser)
def searchService = ComponentAccessor.getComponent(SearchService)
def loggedInUser = ComponentAccessor.jiraAuthenticationContext.loggedInUser
def mailServerManager = ComponentAccessor.mailServerManager
def query = jqlQueryParser.parseQuery("filter=sample_query")
def search = searchService.search(loggedInUser, query, PagerFilter.unlimitedFilter)
def mailServer = mailServerManager.defaultSMTPMailServer
def output = []
def result = [:]
DatabaseUtil.withSql('local_db') { sql ->
output = sql.rows("select email from email_list where username = ?", loggedInUser.name)
}
output.each {
result = it as Map<String,String>
}
if(search.results) {
search.results.each {
def subject = "JIRA Ticket CHECK"
def body = "Test CHECK ${it.key} ${it.summary} "
result.values().findAll { emailAddress ->
def email = new Email(emailAddress.toString())
email.setSubject(subject)
email.setBody(body)
email.setMimeType("text/html")
def threadClassLoader = Thread.currentThread().contextClassLoader
Thread.currentThread().contextClassLoader = mailServer.class.classLoader
mailServer.send(email)
Thread.currentThread().contextClassLoader = threadClassLoader
}
}
}
Please note, this example code is not 100% exact to your environment. Hence, you will need to make the required modifications.
I hope this helps to answer your question. :)
Thank you and Kind regards,
Ram
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.