Skip to content

Instantly share code, notes, and snippets.

@ArnyminerZ
Last active Apr 14, 2020
Embed
What would you like to do?
Use MySQL in Android Kotlin Apps
// MYSQL_USER, MYSQL_PASS and MYSQL_HOST are constants
private fun connect(): MySQLConnector? {
Log.v(TAG, "MySQL Connecting...")
val connector = MySQLConnector()
if (connector.connect(MYSQL_USER, MYSQL_PASS, MYSQL_HOST))
Log.v(TAG, " Connection correct!")
else {
Log.e(TAG, " Connection failed!")
return null
}
return connector
}
fun findUsernameFromUid(uid: String): String? {
val connection = connect() ?: return null
Log.v(TAG, "Getting username...")
val userNames = connection.query(
"MyUsersDatabase"
"users",
listOf(
"username"
),
"`uid`='$uid'",
debug = false
)!!
if (userNames.size > 0) {
val item = userNames.first()
val value = item.findPairItem("username") as String?
if (value == null || value.isEmpty())
return null
return value
} else
return null
}
class MySQLConnector {
companion object {
private const val TAG = "MySQLConnector"
}
private var connection: Connection? = null
/**
* Connect to a MySQL database
* @author ArnyminerZ
* @param username The auth user for the database
* @param password The auth password for the user
* @param host The IP or address to connect
* @param port The port to use, defaults to 3306
* @return If the connection was successful
*/
fun connect(username: String, password: String, host: String, port : Int = 3306) : Boolean {
val connectionProps = Properties()
connectionProps.apply {
put("user", username)
put("password", password)
}
try {
Class.forName("com.mysql.jdbc.Driver").newInstance()
connection = DriverManager.getConnection(
"jdbc:mysql://$host:$port/",
connectionProps
)
return true
} catch (ex: SQLException) {
// handle any errors
ex.printStackTrace()
} catch (ex: Exception) {
// handle any errors
ex.printStackTrace()
}
return false
}
/**
* This is a private function for debugging based on a boolean
*/
private fun debug(message: String, debug: Boolean){
if(debug) Log.d(TAG, message)
}
/**
* This runs a query, but doesn't expect to receive any data
* @param sqlQuery The SQL query to run
* @param debug If a log should be printed
* @return If the query was successful
*/
fun run(sqlQuery: String, debug : Boolean = false) : Boolean {
debug("Checking if connected", debug)
if(connection == null)
throw ConnectionPendingException()
val stmt: Statement?
return try {
debug("Creating statement...", debug)
stmt = connection!!.createStatement()
debug("Executing Query...", debug)
debug("Query: $sqlQuery", debug)
stmt.execute(sqlQuery)
true
} catch (ex: SQLException) {
// handle any errors
ex.printStackTrace()
false
}
}
/**
* This runs a SQL query, and expects to get some data back.
* @param database The name of the database to get the data from
* @param table The name of the table to get the data
* @param keys The keys to select from the database, this is the equivalent to "SELECT []" in SQL
* @param whereAttr This can be left empty, by default it is. It represents the WHERE selector for the query: "WHERE []"
* @param debug If a log should be printed
* @return Returns an ArrayList of ArrayLists of Pairs. This may seem confusing. The first array list contains all the rows. The array lists inside this one contains the column data, and each pair, represents the column name and data. Example: [ ["id":"1", "name":"User1", "uid":"user_1_uid"], ["id":"2", "name":"User2", "uid":"user_2_uid"] ]
*/
@Throws(ConnectionPendingException::class)
fun query(database: String, table: String, keys : List<String>, whereAttr : String = "", debug : Boolean = false) : ArrayList<ArrayList<Pair<String, String?>>>? {
debug("Checking if connected", debug)
if(connection == null)
throw ConnectionPendingException()
val stmt: Statement?
var resultSet: ResultSet?
try {
debug("Creating statement...", debug)
stmt = connection!!.createStatement()
val query = "SELECT * FROM `$database`.`$table`${if(whereAttr.isNotEmpty()) " WHERE $whereAttr" else ""};"
debug("Query: $query", debug)
resultSet = stmt!!.executeQuery(query)
debug("Executing Query...", debug)
if (stmt.execute(query))
resultSet = stmt.resultSet
val list = arrayListOf<ArrayList<Pair<String, String?>>>()
debug("Iterating results...", debug)
var counter = 0
while (resultSet!!.next()) {
debug("Iterating keys...", debug)
val listItem = arrayListOf<Pair<String, String?>>()
for (key in keys) {
val result = resultSet.getString(key)
listItem.add(Pair(key, result))
debug("Adding result \"$result\" with key \"$key\"...", debug)
counter++
}
list.add(listItem)
}
debug("Got $counter results", debug)
debug("List size: ${list.size}", debug)
return list
} catch (ex: SQLException) {
// handle any errors
ex.printStackTrace()
}
return null
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment