ios - Xcode sqlite.swift，尝试使用like子句
问题描述
我使用 sql.swift 并成功设置了数据库并设置了一些条目，每个条目都有一个 id、名字 (Fname)、姓氏 (Lname) 和电话号码 (Pnumber)。我试图使用文本字段作为名字和姓氏作为一种搜索操作。每次文本字段的文本更改时，我都想使用 like 子句执行搜索查询，以在 tableview 中显示可能是用户正在查找的数据库条目。
这是两个文本字段中的任何一个文本更改时调用的函数。
@IBAction func textFieldChanged(_ sender: UITextField) {
var firstNameEntered = true
var lastNameEntered = true
let firstName = firstNameTextField.text! + "%"
let lastName = lastNameTextField.text! + "%"
if(firstName == "%")
{
firstNameEntered = false
}
if(lastName == "%")
{
lastNameEntered = false
}
if(!lastNameEntered && !firstNameEntered)
{
return
}
if(lastNameEntered && !firstNameEntered)
{
for user in try! db.prepare(Customers.filter(Lname.like(lastName)))
{
search.append("(id: \(user[id])), \(user[Fname]) \(user[Lname]), \(user[Pnumber])")
}
searchTableView.reloadData()
return
}
if(!lastNameEntered && firstNameEntered)
{
for user in try! db.prepare(Customers.filter(Fname.like(firstName)))
{
search.append("(id: \(user[id])), \(user[Fname]) \(user[Lname]), \(user[Pnumber])")
}
searchTableView.reloadData()
return
}
for user in try! db.prepare(Customers.filter(Lname.like(lastName) && Fname.like(firstName)))
{
search.append("(id: \(user[id])), \(user[Fname]) \(user[Lname]), \(user[Pnumber])")
}
searchTableView.reloadData()
return
}
在调试器中，它应该正确地进入函数。然而，当它到达查询时，无论输入什么都找不到结果。例如，数据库已经有一个条目（id：1，Fname：Patrick，Lname：Smith，Pnumber：123-456-7890）。然后，当我在名字文本字段中键入 P 时，该函数被调用，并添加了“%”并for user in try! db.prepare(Customers.filter(Fname.like(firstName)))达到了正确的查询。根据 like 子句，第一个数据库条目应该是查询结果的一部分，但不包括在内。关于为什么的任何想法？
我还调用此查询以获取另一个地方的所有条目，并成功返回所有条目
for user in try! db.prepare(Customers) {
customers.append("id: \(user[id]), \(user[Fname]) \(user[Lname]), Number: \(user[Pnumber])")
}
如果需要，这是完整的 ViewController 代码：
import UIKit
import SQLite
class SecondViewController: UIViewController {
var db: Connection!
let Customers = Table("Customers")
var customerCount: Int = 0
let id = Expression<Int64>("Id")
let Fname = Expression<String>("Fname")
let Lname = Expression<String>("Lname")t
let Pnumber = Expression<String>("Pnumber")
var phoneNumberDigits: Int = 0
var customers:[String] = []
var search:[String] = []
override func viewDidLoad() {
super.viewDidLoad()
databaseTableView.delegate = self
databaseTableView.dataSource = self
searchTableView.delegate = self
searchTableView.dataSource = self
firstNameTextField.delegate = self
lastNameTextField.delegate = self
do {
let documentDirectory = try FileManager.default.url(for: .documentDirectory, in: .userDomainMask, appropriateFor: nil, create: true)
let fileUrl = documentDirectory.appendingPathComponent("Customers").appendingPathExtension("sqlite3")
let db = try Connection(fileUrl.path)
print("\(fileUrl.path)")
self.db = db
} catch {
print(error)
}
print("connected")
try! db.run(Customers.create(ifNotExists: true) {
t in
t.column(id, primaryKey: .autoincrement)
t.column(Fname)
t.column(Lname)
t.column(Pnumber, unique: true)
t.unique(Fname, Lname)
print("table created")
})
}
@IBOutlet weak var databaseTableView: UITableView!
@IBOutlet weak var searchTableView: UITableView!
@IBOutlet weak var phoneNumberLabel: UILabel!
@IBOutlet weak var firstNameTextField: UITextField!
@IBOutlet weak var lastNameTextField: UITextField!
@IBOutlet weak var promptTextView: UITextView!
@IBAction func textFieldChanged(_ sender: UITextField) {
var firstNameEntered = true
var lastNameEntered = true
let firstName = firstNameTextField.text! + "%"
let lastName = lastNameTextField.text! + "%"
if(firstName == "%")
{
firstNameEntered = false
}
if(lastName == "%")
{
lastNameEntered = false
}
if(!lastNameEntered && !firstNameEntered)
{
return
}
if(lastNameEntered && !firstNameEntered)
{
for user in try! db.prepare(Customers.filter(Lname.like(lastName)))
{
search.append("(id: \(user[id])), \(user[Fname]) \(user[Lname]), \(user[Pnumber])")
}
searchTableView.reloadData()
return
}
if(!lastNameEntered && firstNameEntered)
{
for user in try! db.prepare(Customers.filter(Fname.like(firstName)))
{
search.append("(id: \(user[id])), \(user[Fname]) \(user[Lname]), \(user[Pnumber])")
}
searchTableView.reloadData()
return
}
for user in try! db.prepare(Customers.filter(Lname.like(lastName) && Fname.like(firstName)))
{
search.append("(id: \(user[id])), \(user[Fname]) \(user[Lname]), \(user[Pnumber])")
}
searchTableView.reloadData()
return
}
@IBAction func addCustomerButton(_ sender: UIButton) {
if(firstNameTextField.text == "")
{
promptTextView.text += "ERROR: No first name given.\n"
return
}
if(lastNameTextField.text == "")
{
promptTextView.text += "ERROR: No last name given.\n"
return
}
if(phoneNumberLabel.text == "")
{
promptTextView.text += "ERROR: No phone number given.\n"
return
}
if(phoneNumberDigits != 10)
{
promptTextView.text += "ERROR: Invalid phone number given. Please enter a 9 digit number\n"
return
}
insertDB()
firstNameTextField.text = ""
lastNameTextField.text = ""
phoneNumberDigits = 0
phoneNumberLabel.text = ""
}
@IBAction func oneButton(_ sender: UIButton) {
if(phoneNumberDigits != 10)
{
if(phoneNumberDigits == 3 || phoneNumberDigits == 6)
{
phoneNumberLabel.text! += "-"
}
phoneNumberLabel.text! += "1"
phoneNumberDigits += 1
}
}
@IBAction func twoButton(_ sender: UIButton) {
if(phoneNumberDigits != 10)
{
if(phoneNumberDigits == 3 || phoneNumberDigits == 6)
{
phoneNumberLabel.text! += "-"
}
phoneNumberLabel.text! += "2"
phoneNumberDigits += 1
}
}
@IBAction func threeButton(_ sender: UIButton) {
if(phoneNumberDigits != 10)
{
if(phoneNumberDigits == 3 || phoneNumberDigits == 6)
{
phoneNumberLabel.text! += "-"
}
phoneNumberLabel.text! += "3"
phoneNumberDigits += 1
}
}
@IBAction func fourButton(_ sender: UIButton) {
if(phoneNumberDigits != 10)
{
if(phoneNumberDigits == 3 || phoneNumberDigits == 6)
{
phoneNumberLabel.text! += "-"
}
phoneNumberLabel.text! += "4"
phoneNumberDigits += 1
}
}
@IBAction func fiveButton(_ sender: UIButton) {
if(phoneNumberDigits != 10)
{
if(phoneNumberDigits == 3 || phoneNumberDigits == 6)
{
phoneNumberLabel.text! += "-"
}
phoneNumberLabel.text! += "5"
phoneNumberDigits += 1
}
}
@IBAction func sixButton(_ sender: UIButton) {
if(phoneNumberDigits != 10)
{
if(phoneNumberDigits == 3 || phoneNumberDigits == 6)
{
phoneNumberLabel.text! += "-"
}
phoneNumberLabel.text! += "6"
phoneNumberDigits += 1
}
}
@IBAction func sevenButton(_ sender: UIButton) {
if(phoneNumberDigits != 10)
{
if(phoneNumberDigits == 3 || phoneNumberDigits == 6)
{
phoneNumberLabel.text! += "-"
}
phoneNumberLabel.text! += "7"
phoneNumberDigits += 1
}
}
@IBAction func eightButton(_ sender: UIButton) {
if(phoneNumberDigits != 10)
{
if(phoneNumberDigits == 3 || phoneNumberDigits == 6)
{
phoneNumberLabel.text! += "-"
}
phoneNumberLabel.text! += "8"
phoneNumberDigits += 1
}
}
@IBAction func nineButton(_ sender: UIButton) {
if(phoneNumberDigits != 10)
{
if(phoneNumberDigits == 3 || phoneNumberDigits == 6)
{
phoneNumberLabel.text! += "-"
}
phoneNumberLabel.text! += "9"
phoneNumberDigits += 1
}
}
@IBAction func zeroButton(_ sender: UIButton) {
if(phoneNumberDigits != 10)
{
if(phoneNumberDigits == 3 || phoneNumberDigits == 6)
{
phoneNumberLabel.text! += "-"
}
phoneNumberLabel.text! += "0"
phoneNumberDigits += 1
}
}
@IBAction func clearButton(_ sender: UIButton) {
phoneNumberLabel.text = ""
phoneNumberDigits = 0
}
// Helper Functions
func insertDB()
{
let phoneNumber = phoneNumberLabel.text?.replacingOccurrences(of: "-", with: "", options:
NSString.CompareOptions.literal, range: nil)
do {
/* let rowid = try db.run(Customers.insert( Fname <- "\(String(describing: firstNameTextField.text))", Lname <- "\(String(describing: lastNameTextField.text))", Pnumber <- "\(String(describing: phoneNumber))"))
*/
print("inserted id: \(String(describing: rowid))")
let customerString = "id: \(String(describing: rowid)), \(String(describing: firstNameTextField.text!)) \(String(describing: lastNameTextField.text!)), \(String(describing: phoneNumberLabel.text!))"
promptTextView.text += customerString + ", added to database.\n\n"
} catch let Result.error(message, code, statement) where code == SQLITE_CONSTRAINT {
promptTextView.text += "ERROR: invalid input, constraint failed: \(message), in \(String(describing: statement))\n\n"
}
catch let error{
print("insertion failed: \(error)")
promptTextView.text += "ERROR: Insertion failed!."
return
}
for user in try! db.prepare(Customers) {
customers.append("id: \(user[id]), \(user[Fname]) \(user[Lname]), Number: \(user[Pnumber])")
// id: 1, email: alice@mac.com, name: Optional("Alice")
}
databaseTableView.reloadData()
}
// Touch Events
override func touchesBegan(_ touches: Set<UITouch>, with event: UIEvent?) {
self.view.endEditing(true)
}
}
extension SecondViewController: UITableViewDelegate {
func tableView(_ tableView: UITableView, didSelectRowAt indexPath: IndexPath) {
}
}
extension SecondViewController: UITableViewDataSource {
func tableView(_ tableView: UITableView, numberOfRowsInSection section: Int) -> Int {
if(tableView == databaseTableView)
{
return customers.count
}
return search.count
}
func tableView(_ tableView: UITableView, cellForRowAt indexPath: IndexPath) -> UITableViewCell {
let cell = databaseTableView.dequeueReusableCell(withIdentifier: "cell")
if(tableView == databaseTableView)
{
cell?.textLabel?.text = customers[indexPath.row]
} else { cell?.textLabel?.text = search[indexPath.row] }
return cell!
}
}
extension SecondViewController: UITextFieldDelegate {
// Text Field Delegate Methods
func textFieldShouldReturn(_ textField: UITextField) -> Bool {
textField.resignFirstResponder()
return true
}
func textFieldDidBeginEditing(_ textField: UITextField) {
textField.keyboardType = UIKeyboardType.alphabet
}
}
解决方案
推荐阅读
- java - 使用Java根据条件返回不同类型的对象
- mongodb - $match 查询不适用于 mongodb 中的查找数组
- nginx - 在自己的域上重写和“托管”网址
- android - 为什么jw播放器不会全屏？
- android - 如何解决版本冲突的依赖问题？
- sql - 检查任何三个连续交易是否超过指定金额
- ruby - 我正在使用 MediaRecorder API 录制语音，为什么块小于实际大小？
- python - Keras mode.fit - 为什么每次新运行都会产生不同的结果（准确度和损失）？
- c - 为什么在进行远程过程调用时，UDP 优于 TCP？
- google-cloud-platform - 如何在现有的谷歌云虚拟机实例上启用嵌套虚拟化？