JDBC数据库连接

#创建数据库
#CREATE DATABASE study_mybatis;

#选择数据库
#use study_mybatis;

#查看所有的数据库
#SHOW database;

#查看数据库表
#SHOW TABLEs;

#创建数据库表

DROP TABLE IF EXISTS `tb_user`;
CREATE TABLE `tb_user` (
  `id` BIGINT(20) NOT NULL AUTO_INCREMENT,
  `user_name` VARCHAR(100) DEFAULT NULL COMMENT ‘用户名‘,
  `password` VARCHAR(100) DEFAULT NULL COMMENT,
  `name` VARCHAR(100) DEFAULT NULL COMMENT,
  `age` INT(10) DEFAULT NULL COMMENT ,
  `sex` TINYINT(1) DEFAULT NULL COMMENT ‘性别，1男性，2女性‘,
  `birthday` DATE DEFAULT NULL COMMENT,
  `created` DATETIME DEFAULT NULL COMMENT ,
  `updated` DATETIME DEFAULT NULL COMMENT ,
  PRIMARY KEY (`id`),
  UNIQUE KEY `username` (`user_name`)
) ENGINE=INNODB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;

#插入数据

INSERT INTO `tb_user` VALUES (‘1‘, ‘zhangsan‘, ‘123456‘, ‘张三‘, ‘30‘, ‘1‘, ‘1984-08-08‘, ‘2014-09-19 16:56:04‘, ‘2014-09-21 11:24:59‘);
INSERT INTO `tb_user` VALUES (‘2‘, ‘lisi‘, ‘123456‘, ‘李四‘, ‘21‘, ‘2‘, ‘1991-01-01‘, ‘2014-09-19 16:56:04‘, ‘2014-09-19 16:56:04‘);
INSERT INTO `tb_user` VALUES (‘3‘, ‘wangwu‘, ‘123456‘, ‘王五‘, ‘22‘, ‘2‘, ‘1989-01-01‘, ‘2014-09-19 16:56:04‘, ‘2014-09-19 16:56:04‘);

#查看表中数据

SELECT * FROM tb_user;

        String MYSQL_DRIVER = "com.mysql.jdbc.Driver";
        String url = "jdbc:mysql://127.0.0.1:3306/study_mybatis";
        String user = "root";
        String password = "root123";

        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        try {
            Class.forName(MYSQL_DRIVER);
            //创建数据库连接
            connection = DriverManager.getConnection(url, user, password);
            
            //创建Statement对象
            String sql = "select * from tb_user where user_name = ?";
            preparedStatement = connection.prepareStatement(sql);

            //设置参数 ，下标从1开始
            preparedStatement.setString(1, "zhangsan");
            
            //执行sql 变量结果集 
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                System.out.println("user_name:"+resultSet.getString("user_name"));
                System.out.println("name:"+resultSet.getString("name"));

            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (null != resultSet) {
                resultSet.close();
            }
            if(null != preparedStatement){
                preparedStatement.close();
            }
            if(null != connection){
                connection.close();
            }
        }