JDBC

2021-07-06 09:50:19

工具：mysql 5.6.19,mysql-connector-java-5.1.48.jar,android stuido&android studio自带模拟器

1.在mysql数据库中新建表customer,并创建相关字段

SQL命令如下

create table customer(username varchar(20),password varchar(20),email varchar(40));

alter table customer add id int(5) auto_increment primary key;

mysql> insert into customer(username,password,email) values(‘张三‘,‘123456‘,‘zhangsan@5429.com‘);

insert into customer(username,password,email) values(‘李四‘,‘234567‘,‘lisi@5429.com‘);

insert into customer(username,password,email) values(‘王五‘,‘345678‘,‘wangwu@5429.com‘);

insert into customer(username,password,email) values(‘陈六‘,‘456789‘,‘chenliu@5429.com‘);

select * from customer;

2.JDBC连接数据库

------>页面布局

------>设置按钮点击监听

------>导入mysql-connector-java-5.1.48.jar包，搜索下载即可

复制粘贴到lib文件下，右键，add as library...

------>JDBC连接数据库

（1），添加网络许可<uses-permission android:name="android.permission.INTERNET"/>

（2）Util类代码(待优化)

package com.vocus.jdbctest

import java.lang.Exception
import java.sql.Connection
import java.sql.DriverManager

class Util {
    var conn:Connection?=null
    val driver="com.mysql.jdbc.Drvier"
    val url="jdbc:mysql://10.0.2.2:3306/test"
    val user="root"  //root用户
    val psd="rs123456" //自己的密码
    fun connection():Connection?{
        try{
            Class.forName(driver).newInstance()
        }catch(e:Exception){
            e.printStackTrace()
        }
        try{
            conn=DriverManager.getConnection(url,user,psd)
        }catch (e:Exception){
            e.printStackTrace()
        }
        return conn
    }
}

View Code

（3）针对数据库表customer的查询(待优化)

class Customer {
    open val username:String?=null //权宜之计
    open val password:String?=null
    open val email:String?=null

    override fun toString(): String {
        return "Customer(username=$username, password=$password, email=$email)"
    }

}

View Code

package com.vocus.jdbctest

import java.lang.Exception

class CustomerForQuery {
    fun query(sql: String, vararg args: Any): Customer?{//sql语句如select username from customer where id=?,args不确定参数

        try {
//            Thread({
                var conn = Util().connection()
                var ps = conn!!.prepareStatement(sql)

                for (i in 0 until args.size) {//设置sql参数
                    ps.setObject(i + 1, args[i])
                }

                var resultSet = ps.executeQuery()//获得查询结果集

                var rsmd = resultSet.getMetaData()
                var columns = rsmd.columnCount //获得结果集列数

                if (resultSet.next()) {
                    var cust = Customer()
                    for (i in 0 until columns) {
                        var value = resultSet.getObject(i + 1)  //获得查询的值
                        var columnsName = rsmd.getColumnName(i + 1) //获得值对应的列名，即字段名
                        //println("查询结果，${value}+${columnsName}")
                        val field = Customer::class.java.getDeclaredField(columnsName)
                        field.setAccessible(true)
                        field.set(cust, value)
                    }
                    println("已执行查询语句...")
                    return cust
                }
//            }).start()
        } catch (e: Exception) {
            e.printStackTrace()
        }
        return null
    }
}

View Code

（4）添加按钮点击监听执行

package com.vocus.jdbctest
import android.os.Bundle
import androidx.appcompat.app.AppCompatActivity
import kotlinx.android.synthetic.main.activity_main.*
import kotlin.concurrent.thread


class MainActivity : AppCompatActivity() {

    override fun onCreate(savedInstanceState: Bundle?) {
        super.onCreate(savedInstanceState)
        setContentView(R.layout.activity_main)

        connBtn.setOnClickListener {
            thread{
                var cust = CustomerForQuery().query("select username from customer where id=?", "3")
                println(cust)
            }
        }
    }

}

（5）优化关闭连接，引入泛型，实现针对不同表的通用查询等

------>DAO模式

（1）建立DAO抽象类(BaseDAO)

对于数据库的增删改查等基本操作封装在此类中

（2）针对具体的表提供接口(CustomerDAO)

用于说明具体的需求

interface CustomerDAO {
    //查询用户名是否存在
    fun logcheck(username:String,password:String):Boolean
}

View Code

（3）实现接口的类（CustomerDAOimpl）

用于需求的实现

class CustomerDAOImpl :BaseDAO(),CustomerDAO {
    override fun logcheck(username: String, password: String): Boolean {
        var cust:Customer?=null
        try{
            cust=query("select password from customer where username=?",username)
        }catch (e:Exception){
            println("用户名不存在...")
            return false
        }
        if(cust!!.password==password){
            return true
        }
        else{
            println("账号和用户名不匹配...")
            return false
        }
    }
}

View Code

class MainActivity : AppCompatActivity() {

    override fun onCreate(savedInstanceState: Bundle?) {
        super.onCreate(savedInstanceState)
        setContentView(R.layout.activity_main)

        connBtn.setOnClickListener {
            thread{
                //var cust = CustomerForQuery().query("select password from customer where username=?", "张三")
                //println(cust)
                var exist=CustomerDAOImpl().logcheck("张三","123456")
                println("exist?:${exist}")
            }
        }
    }

}

View Code

JDBC<android studio,kotlin>

码农公寓

相关文章