sqlite安装与学习

发布时间：2020-12-12 19:32:52 所属栏目：百科来源：网络整理

导读：图形化管理工具：sqlite developer 创建数据库：sqlite3 test.db 创建表格： create table employee( id integer primary key autoincrement, name varchar(20), pwd varchar(20), age integer ); 插入数据： insert into employee(name,pwd,age) values('zh

图形化管理工具：sqlite developer

创建数据库：sqlite3 test.db

创建表格：
create table employee(
id integer primary key autoincrement,
name varchar(20),
pwd varchar(20),
age integer
);

插入数据：
insert into employee(name,pwd,age) values('zhaodeng','1234','25');
insert into employee(name,age) values('Tom','30');

查询显示数据
select * from employee;

cmd操作展示：

Microsoft Windows [版本 6.1.7601]
版权所有 (c) 2009 Microsoft Corporation。保留所有权利。
C:UsersZhaoD>d:

D:&;cd D:Jdk-eclipsejee-tomcat-antsqlite

D:Jdk-eclipsejee-tomcat-antsqlite>sqlite3 test.db
SQLite version 3.11.1 2016-03-03 16:17:53
Enter ".help" for usage hints.
sqlite> create table employee(
...> id integer primary key autoincrement,
...> name varchar(20),
...> pwd varchar(20),
...> age integer
...> );
sqlite> insert into employee(name,'25');
sqlite> insert into employee(name,'30');
sqlite> select * from employee;
1|zhaodeng|1234|25
2|Tom|1234|30

sqlite>

------------------------------------------------------------------------------------

JDBC连接sqlite数据库程序

package bo;

import java.sql.Statement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class DbTEst {

	public static void main(String[] args) {
		// TODO Auto-generated method stub

		List<Employee> employees = fetchData();
		for (Employee e : employees) {
			System.out.println(e.toString());
		}
		
		Employee employee = login("Tom","1234");
		if(employee!=null){
			System.out.println("登录成功，欢迎"+employee.getName());
			System.out.println(employee.toString());
		}else{
			System.out.println("登录失败");
		}

	}

	private static Employee login(String name,String pwd) {
		Employee employee = null;

		Connection connection = null;
		try {
			Class.forName("org.sqlite.JDBC");
			connection = DriverManager.getConnection("jdbc:sqlite:D:/Jdk-eclipsejee-tomcat-ant/sqlite/test.db");

			String string = "select * from employee where name =? and pwd =?";
			PreparedStatement statement = (PreparedStatement) connection.prepareStatement(string);
			statement.setString(1,name);
			statement.setString(2,pwd);

			ResultSet resultSet = statement.executeQuery();
			if (resultSet.next()) {
				employee = new Employee(resultSet.getInt(1),resultSet.getString(2),resultSet.getString(3),resultSet.getInt(4));
			}
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally {
			if (connection != null) {
				try {
					connection.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
		}
		return employee;

	}

	private static List<Employee> fetchData() {
		List<Employee> employees = new ArrayList<Employee>();
		Employee employee = null;

		Connection connection = null;
		try {
			Class.forName("org.sqlite.JDBC");
			connection = DriverManager.getConnection("jdbc:sqlite:D:/Jdk-eclipsejee-tomcat-ant/sqlite/test.db");
			Statement statement = (Statement) connection.createStatement();

			String string = "select * from employee";
			ResultSet rSet = statement.executeQuery(string);
			while (rSet.next()) {
				employee = new Employee(rSet.getInt(1),rSet.getString(2),rSet.getString(3),rSet.getInt(4));
				employees.add(employee);
			}

		} catch (ClassNotFoundException e) {
			e.printStackTrace();

		} catch (SQLException e) {
			// TODO: handle exception
			e.printStackTrace();
		} finally {
			if (connection != null) {
				try {
					connection.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}

		}
		return employees;
	}

}

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!