[java]jdbc操作mysql

jdbc driver

创建一个driver实例
通过driverManager注册
driverManager.getConnection
conn.prepareStatement

    public static void main(String[] args) throws Exception {
        InputStream is = ClassLoader.getSystemClassLoader().getResourceAsStream("jdbctest.properties");

        Properties properties = new Properties();
        properties.load(is);


        String user = properties.getProperty("user");
        String password = properties.getProperty("password");
        String url = properties.getProperty("url");
        String driverClass = properties.getProperty("driverClass");
        Class.forName(driverClass);
        Connection conn = DriverManager.getConnection(url,user,password);
        System.out.println(conn);
    }

jdbctest.properties

user=root
password=root
url=jdbc:mysql://localhost:3306/book21
driverClass=com.mysql.jdbc.Driver
initialSize=5
maxActive=10

py - pymysql防止注入

fetchone(): 该方法获取下一个查询结果集。结果集是一个对象
fetchall():   接收全部的返回结果行.


# SQL 查询语句
sql = "SELECT * FROM EMPLOYEE \
       WHERE INCOME > %s" % (1000)
try:
   # 执行SQL语句
   cursor.execute(sql)
   # 获取所有记录列表
   results = cursor.fetchall()

go

单行查询， Db.QueryRow
多行查询， Db.Query

//查询
sqlstr := "select id, name, age from user where id > ?"
rows, err := stmt.Query(0)

//更新
Db.Prepare(sql string) (*sql.Stmt, error)
Stmt.Exec()

连接池技术

获取连接, 释放连接

导入mysql driver, 导入druid连接池,导入Dbutils(queryRunner)

public class JdbcUtils {
    private static DataSource dataSource;

    static {
        Properties properties = new Properties();
        InputStream inputStream = JdbcUtils.class.getClassLoader().getResourceAsStream("jdbc.properties");
        try {
            properties.load(inputStream);
            dataSource = DruidDataSourceFactory.createDataSource(properties);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public static Connection getConnection() {
        Connection conn = null;
        try {
            conn = dataSource.getConnection();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return conn;
    }

    public static void close(Connection conn) {
        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }
}

druid连接db

queryForOne
queryForList
queryForSingleValue

update  (Insert/Update/Delete) 返回受影响行数

注意点:

- 正确的
        String sql = "insert into t_book(`name`,`author`,`price`,`sales`,`stock`,`img_path`) values(?,?,?,?,?,?)";

- 错误的
        String sql = "insert into t_book(name ,author ,price ,sales ,stock ,img_path ) values('?' , '?' , ? , ? , ? , '?')";